1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot._typing import E
  25from sqlglot.errors import ParseError
  26from sqlglot.helper import (
  27    AutoName,
  28    camel_to_snake_case,
  29    ensure_collection,
  30    ensure_list,
  31    seq_get,
  32    subclasses,
  33)
  34from sqlglot.tokens import Token
  35
  36if t.TYPE_CHECKING:
  37    from sqlglot.dialects.dialect import DialectType
  38
  39
  40class _Expression(type):
  41    def __new__(cls, clsname, bases, attrs):
  42        klass = super().__new__(cls, clsname, bases, attrs)
  43
  44        # When an Expression class is created, its key is automatically set to be
  45        # the lowercase version of the class' name.
  46        klass.key = clsname.lower()
  47
  48        # This is so that docstrings are not inherited in pdoc
  49        klass.__doc__ = klass.__doc__ or ""
  50
  51        return klass
  52
  53
  54class Expression(metaclass=_Expression):
  55    """
  56    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  57    context, such as its child expressions, their names (arg keys), and whether a given child expression
  58    is optional or not.
  59
  60    Attributes:
  61        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  62            and representing expressions as strings.
  63        arg_types: determines what arguments (child nodes) are supported by an expression. It
  64            maps arg keys to booleans that indicate whether the corresponding args are optional.
  65        parent: a reference to the parent expression (or None, in case of root expressions).
  66        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  67            uses to refer to it.
  68        comments: a list of comments that are associated with a given expression. This is used in
  69            order to preserve comments when transpiling SQL code.
  70        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  71            optimizer, in order to enable some transformations that require type information.
  72        meta: a dictionary that can be used to store useful metadata for a given expression.
  73
  74    Example:
  75        >>> class Foo(Expression):
  76        ...     arg_types = {"this": True, "expression": False}
  77
  78        The above definition informs us that Foo is an Expression that requires an argument called
  79        "this" and may also optionally receive an argument called "expression".
  80
  81    Args:
  82        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  83    """
  84
  85    key = "expression"
  86    arg_types = {"this": True}
  87    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  88
  89    def __init__(self, **args: t.Any):
  90        self.args: t.Dict[str, t.Any] = args
  91        self.parent: t.Optional[Expression] = None
  92        self.arg_key: t.Optional[str] = None
  93        self.comments: t.Optional[t.List[str]] = None
  94        self._type: t.Optional[DataType] = None
  95        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  96        self._hash: t.Optional[int] = None
  97
  98        for arg_key, value in self.args.items():
  99            self._set_parent(arg_key, value)
 100
 101    def __eq__(self, other) -> bool:
 102        return type(self) is type(other) and hash(self) == hash(other)
 103
 104    @property
 105    def hashable_args(self) -> t.Any:
 106        return frozenset(
 107            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 108            for k, v in self.args.items()
 109            if not (v is None or v is False or (type(v) is list and not v))
 110        )
 111
 112    def __hash__(self) -> int:
 113        if self._hash is not None:
 114            return self._hash
 115
 116        return hash((self.__class__, self.hashable_args))
 117
 118    @property
 119    def this(self):
 120        """
 121        Retrieves the argument with key "this".
 122        """
 123        return self.args.get("this")
 124
 125    @property
 126    def expression(self):
 127        """
 128        Retrieves the argument with key "expression".
 129        """
 130        return self.args.get("expression")
 131
 132    @property
 133    def expressions(self):
 134        """
 135        Retrieves the argument with key "expressions".
 136        """
 137        return self.args.get("expressions") or []
 138
 139    def text(self, key) -> str:
 140        """
 141        Returns a textual representation of the argument corresponding to "key". This can only be used
 142        for args that are strings or leaf Expression instances, such as identifiers and literals.
 143        """
 144        field = self.args.get(key)
 145        if isinstance(field, str):
 146            return field
 147        if isinstance(field, (Identifier, Literal, Var)):
 148            return field.this
 149        if isinstance(field, (Star, Null)):
 150            return field.name
 151        return ""
 152
 153    @property
 154    def is_string(self) -> bool:
 155        """
 156        Checks whether a Literal expression is a string.
 157        """
 158        return isinstance(self, Literal) and self.args["is_string"]
 159
 160    @property
 161    def is_number(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a number.
 164        """
 165        return isinstance(self, Literal) and not self.args["is_string"]
 166
 167    @property
 168    def is_int(self) -> bool:
 169        """
 170        Checks whether a Literal expression is an integer.
 171        """
 172        if self.is_number:
 173            try:
 174                int(self.name)
 175                return True
 176            except ValueError:
 177                pass
 178        return False
 179
 180    @property
 181    def is_star(self) -> bool:
 182        """Checks whether an expression is a star."""
 183        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 184
 185    @property
 186    def alias(self) -> str:
 187        """
 188        Returns the alias of the expression, or an empty string if it's not aliased.
 189        """
 190        if isinstance(self.args.get("alias"), TableAlias):
 191            return self.args["alias"].name
 192        return self.text("alias")
 193
 194    @property
 195    def alias_column_names(self) -> t.List[str]:
 196        table_alias = self.args.get("alias")
 197        if not table_alias:
 198            return []
 199        return [c.name for c in table_alias.args.get("columns") or []]
 200
 201    @property
 202    def name(self) -> str:
 203        return self.text("this")
 204
 205    @property
 206    def alias_or_name(self) -> str:
 207        return self.alias or self.name
 208
 209    @property
 210    def output_name(self) -> str:
 211        """
 212        Name of the output column if this expression is a selection.
 213
 214        If the Expression has no output name, an empty string is returned.
 215
 216        Example:
 217            >>> from sqlglot import parse_one
 218            >>> parse_one("SELECT a").expressions[0].output_name
 219            'a'
 220            >>> parse_one("SELECT b AS c").expressions[0].output_name
 221            'c'
 222            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 223            ''
 224        """
 225        return ""
 226
 227    @property
 228    def type(self) -> t.Optional[DataType]:
 229        return self._type
 230
 231    @type.setter
 232    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 233        if dtype and not isinstance(dtype, DataType):
 234            dtype = DataType.build(dtype)
 235        self._type = dtype  # type: ignore
 236
 237    @property
 238    def meta(self) -> t.Dict[str, t.Any]:
 239        if self._meta is None:
 240            self._meta = {}
 241        return self._meta
 242
 243    def __deepcopy__(self, memo):
 244        copy = self.__class__(**deepcopy(self.args))
 245        if self.comments is not None:
 246            copy.comments = deepcopy(self.comments)
 247
 248        if self._type is not None:
 249            copy._type = self._type.copy()
 250
 251        if self._meta is not None:
 252            copy._meta = deepcopy(self._meta)
 253
 254        return copy
 255
 256    def copy(self):
 257        """
 258        Returns a deep copy of the expression.
 259        """
 260        new = deepcopy(self)
 261        new.parent = self.parent
 262        return new
 263
 264    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 265        if self.comments is None:
 266            self.comments = []
 267        if comments:
 268            self.comments.extend(comments)
 269
 270    def append(self, arg_key: str, value: t.Any) -> None:
 271        """
 272        Appends value to arg_key if it's a list or sets it as a new list.
 273
 274        Args:
 275            arg_key (str): name of the list expression arg
 276            value (Any): value to append to the list
 277        """
 278        if not isinstance(self.args.get(arg_key), list):
 279            self.args[arg_key] = []
 280        self.args[arg_key].append(value)
 281        self._set_parent(arg_key, value)
 282
 283    def set(self, arg_key: str, value: t.Any) -> None:
 284        """
 285        Sets arg_key to value.
 286
 287        Args:
 288            arg_key: name of the expression arg.
 289            value: value to set the arg to.
 290        """
 291        if value is None:
 292            self.args.pop(arg_key, None)
 293            return
 294
 295        self.args[arg_key] = value
 296        self._set_parent(arg_key, value)
 297
 298    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 299        if hasattr(value, "parent"):
 300            value.parent = self
 301            value.arg_key = arg_key
 302        elif type(value) is list:
 303            for v in value:
 304                if hasattr(v, "parent"):
 305                    v.parent = self
 306                    v.arg_key = arg_key
 307
 308    @property
 309    def depth(self) -> int:
 310        """
 311        Returns the depth of this tree.
 312        """
 313        if self.parent:
 314            return self.parent.depth + 1
 315        return 0
 316
 317    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 318        """Yields the key and expression for all arguments, exploding list args."""
 319        for k, vs in self.args.items():
 320            if type(vs) is list:
 321                for v in vs:
 322                    if hasattr(v, "parent"):
 323                        yield k, v
 324            else:
 325                if hasattr(vs, "parent"):
 326                    yield k, vs
 327
 328    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 329        """
 330        Returns the first node in this tree which matches at least one of
 331        the specified types.
 332
 333        Args:
 334            expression_types: the expression type(s) to match.
 335            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 336
 337        Returns:
 338            The node which matches the criteria or None if no such node was found.
 339        """
 340        return next(self.find_all(*expression_types, bfs=bfs), None)
 341
 342    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 343        """
 344        Returns a generator object which visits all nodes in this tree and only
 345        yields those that match at least one of the specified expression types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The generator object.
 353        """
 354        for expression, *_ in self.walk(bfs=bfs):
 355            if isinstance(expression, expression_types):
 356                yield expression
 357
 358    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 359        """
 360        Returns a nearest parent matching expression_types.
 361
 362        Args:
 363            expression_types: the expression type(s) to match.
 364
 365        Returns:
 366            The parent node.
 367        """
 368        ancestor = self.parent
 369        while ancestor and not isinstance(ancestor, expression_types):
 370            ancestor = ancestor.parent
 371        return t.cast(E, ancestor)
 372
 373    @property
 374    def parent_select(self) -> t.Optional[Select]:
 375        """
 376        Returns the parent select statement.
 377        """
 378        return self.find_ancestor(Select)
 379
 380    @property
 381    def same_parent(self) -> bool:
 382        """Returns if the parent is the same class as itself."""
 383        return type(self.parent) is self.__class__
 384
 385    def root(self) -> Expression:
 386        """
 387        Returns the root expression of this tree.
 388        """
 389        expression = self
 390        while expression.parent:
 391            expression = expression.parent
 392        return expression
 393
 394    def walk(self, bfs=True, prune=None):
 395        """
 396        Returns a generator object which visits all nodes in this tree.
 397
 398        Args:
 399            bfs (bool): if set to True the BFS traversal order will be applied,
 400                otherwise the DFS traversal will be used instead.
 401            prune ((node, parent, arg_key) -> bool): callable that returns True if
 402                the generator should stop traversing this branch of the tree.
 403
 404        Returns:
 405            the generator object.
 406        """
 407        if bfs:
 408            yield from self.bfs(prune=prune)
 409        else:
 410            yield from self.dfs(prune=prune)
 411
 412    def dfs(self, parent=None, key=None, prune=None):
 413        """
 414        Returns a generator object which visits all nodes in this tree in
 415        the DFS (Depth-first) order.
 416
 417        Returns:
 418            The generator object.
 419        """
 420        parent = parent or self.parent
 421        yield self, parent, key
 422        if prune and prune(self, parent, key):
 423            return
 424
 425        for k, v in self.iter_expressions():
 426            yield from v.dfs(self, k, prune)
 427
 428    def bfs(self, prune=None):
 429        """
 430        Returns a generator object which visits all nodes in this tree in
 431        the BFS (Breadth-first) order.
 432
 433        Returns:
 434            The generator object.
 435        """
 436        queue = deque([(self, self.parent, None)])
 437
 438        while queue:
 439            item, parent, key = queue.popleft()
 440
 441            yield item, parent, key
 442            if prune and prune(item, parent, key):
 443                continue
 444
 445            for k, v in item.iter_expressions():
 446                queue.append((v, item, k))
 447
 448    def unnest(self):
 449        """
 450        Returns the first non parenthesis child or self.
 451        """
 452        expression = self
 453        while type(expression) is Paren:
 454            expression = expression.this
 455        return expression
 456
 457    def unalias(self):
 458        """
 459        Returns the inner expression if this is an Alias.
 460        """
 461        if isinstance(self, Alias):
 462            return self.this
 463        return self
 464
 465    def unnest_operands(self):
 466        """
 467        Returns unnested operands as a tuple.
 468        """
 469        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 470
 471    def flatten(self, unnest=True):
 472        """
 473        Returns a generator which yields child nodes who's parents are the same class.
 474
 475        A AND B AND C -> [A, B, C]
 476        """
 477        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 478            if not type(node) is self.__class__:
 479                yield node.unnest() if unnest else node
 480
 481    def __str__(self) -> str:
 482        return self.sql()
 483
 484    def __repr__(self) -> str:
 485        return self._to_s()
 486
 487    def sql(self, dialect: DialectType = None, **opts) -> str:
 488        """
 489        Returns SQL string representation of this tree.
 490
 491        Args:
 492            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 493            opts: other `sqlglot.generator.Generator` options.
 494
 495        Returns:
 496            The SQL string.
 497        """
 498        from sqlglot.dialects import Dialect
 499
 500        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 501
 502    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 503        indent = "" if not level else "\n"
 504        indent += "".join(["  "] * level)
 505        left = f"({self.key.upper()} "
 506
 507        args: t.Dict[str, t.Any] = {
 508            k: ", ".join(
 509                v._to_s(hide_missing=hide_missing, level=level + 1)
 510                if hasattr(v, "_to_s")
 511                else str(v)
 512                for v in ensure_list(vs)
 513                if v is not None
 514            )
 515            for k, vs in self.args.items()
 516        }
 517        args["comments"] = self.comments
 518        args["type"] = self.type
 519        args = {k: v for k, v in args.items() if v or not hide_missing}
 520
 521        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 522        right += ")"
 523
 524        return indent + left + right
 525
 526    def transform(self, fun, *args, copy=True, **kwargs):
 527        """
 528        Recursively visits all tree nodes (excluding already transformed ones)
 529        and applies the given transformation function to each node.
 530
 531        Args:
 532            fun (function): a function which takes a node as an argument and returns a
 533                new transformed node or the same node without modifications. If the function
 534                returns None, then the corresponding node will be removed from the syntax tree.
 535            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 536                modified in place.
 537
 538        Returns:
 539            The transformed tree.
 540        """
 541        node = self.copy() if copy else self
 542        new_node = fun(node, *args, **kwargs)
 543
 544        if new_node is None or not isinstance(new_node, Expression):
 545            return new_node
 546        if new_node is not node:
 547            new_node.parent = node.parent
 548            return new_node
 549
 550        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 551        return new_node
 552
 553    @t.overload
 554    def replace(self, expression: E) -> E:
 555        ...
 556
 557    @t.overload
 558    def replace(self, expression: None) -> None:
 559        ...
 560
 561    def replace(self, expression):
 562        """
 563        Swap out this expression with a new expression.
 564
 565        For example::
 566
 567            >>> tree = Select().select("x").from_("tbl")
 568            >>> tree.find(Column).replace(Column(this="y"))
 569            (COLUMN this: y)
 570            >>> tree.sql()
 571            'SELECT y FROM tbl'
 572
 573        Args:
 574            expression: new node
 575
 576        Returns:
 577            The new expression or expressions.
 578        """
 579        if not self.parent:
 580            return expression
 581
 582        parent = self.parent
 583        self.parent = None
 584
 585        replace_children(parent, lambda child: expression if child is self else child)
 586        return expression
 587
 588    def pop(self: E) -> E:
 589        """
 590        Remove this expression from its AST.
 591
 592        Returns:
 593            The popped expression.
 594        """
 595        self.replace(None)
 596        return self
 597
 598    def assert_is(self, type_: t.Type[E]) -> E:
 599        """
 600        Assert that this `Expression` is an instance of `type_`.
 601
 602        If it is NOT an instance of `type_`, this raises an assertion error.
 603        Otherwise, this returns this expression.
 604
 605        Examples:
 606            This is useful for type security in chained expressions:
 607
 608            >>> import sqlglot
 609            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 610            'SELECT x, z FROM y'
 611        """
 612        assert isinstance(self, type_)
 613        return self
 614
 615    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 616        """
 617        Checks if this expression is valid (e.g. all mandatory args are set).
 618
 619        Args:
 620            args: a sequence of values that were used to instantiate a Func expression. This is used
 621                to check that the provided arguments don't exceed the function argument limit.
 622
 623        Returns:
 624            A list of error messages for all possible errors that were found.
 625        """
 626        errors: t.List[str] = []
 627
 628        for k in self.args:
 629            if k not in self.arg_types:
 630                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 631        for k, mandatory in self.arg_types.items():
 632            v = self.args.get(k)
 633            if mandatory and (v is None or (isinstance(v, list) and not v)):
 634                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 635
 636        if (
 637            args
 638            and isinstance(self, Func)
 639            and len(args) > len(self.arg_types)
 640            and not self.is_var_len_args
 641        ):
 642            errors.append(
 643                f"The number of provided arguments ({len(args)}) is greater than "
 644                f"the maximum number of supported arguments ({len(self.arg_types)})"
 645            )
 646
 647        return errors
 648
 649    def dump(self):
 650        """
 651        Dump this Expression to a JSON-serializable dict.
 652        """
 653        from sqlglot.serde import dump
 654
 655        return dump(self)
 656
 657    @classmethod
 658    def load(cls, obj):
 659        """
 660        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 661        """
 662        from sqlglot.serde import load
 663
 664        return load(obj)
 665
 666
 667IntoType = t.Union[
 668    str,
 669    t.Type[Expression],
 670    t.Collection[t.Union[str, t.Type[Expression]]],
 671]
 672ExpOrStr = t.Union[str, Expression]
 673
 674
 675class Condition(Expression):
 676    def and_(
 677        self,
 678        *expressions: t.Optional[ExpOrStr],
 679        dialect: DialectType = None,
 680        copy: bool = True,
 681        **opts,
 682    ) -> Condition:
 683        """
 684        AND this condition with one or multiple expressions.
 685
 686        Example:
 687            >>> condition("x=1").and_("y=1").sql()
 688            'x = 1 AND y = 1'
 689
 690        Args:
 691            *expressions: the SQL code strings to parse.
 692                If an `Expression` instance is passed, it will be used as-is.
 693            dialect: the dialect used to parse the input expression.
 694            copy: whether or not to copy the involved expressions (only applies to Expressions).
 695            opts: other options to use to parse the input expressions.
 696
 697        Returns:
 698            The new And condition.
 699        """
 700        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 701
 702    def or_(
 703        self,
 704        *expressions: t.Optional[ExpOrStr],
 705        dialect: DialectType = None,
 706        copy: bool = True,
 707        **opts,
 708    ) -> Condition:
 709        """
 710        OR this condition with one or multiple expressions.
 711
 712        Example:
 713            >>> condition("x=1").or_("y=1").sql()
 714            'x = 1 OR y = 1'
 715
 716        Args:
 717            *expressions: the SQL code strings to parse.
 718                If an `Expression` instance is passed, it will be used as-is.
 719            dialect: the dialect used to parse the input expression.
 720            copy: whether or not to copy the involved expressions (only applies to Expressions).
 721            opts: other options to use to parse the input expressions.
 722
 723        Returns:
 724            The new Or condition.
 725        """
 726        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 727
 728    def not_(self, copy: bool = True):
 729        """
 730        Wrap this condition with NOT.
 731
 732        Example:
 733            >>> condition("x=1").not_().sql()
 734            'NOT x = 1'
 735
 736        Args:
 737            copy: whether or not to copy this object.
 738
 739        Returns:
 740            The new Not instance.
 741        """
 742        return not_(self, copy=copy)
 743
 744    def as_(
 745        self,
 746        alias: str | Identifier,
 747        quoted: t.Optional[bool] = None,
 748        dialect: DialectType = None,
 749        copy: bool = True,
 750        **opts,
 751    ) -> Alias:
 752        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 753
 754    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 755        this = self.copy()
 756        other = convert(other, copy=True)
 757        if not isinstance(this, klass) and not isinstance(other, klass):
 758            this = _wrap(this, Binary)
 759            other = _wrap(other, Binary)
 760        if reverse:
 761            return klass(this=other, expression=this)
 762        return klass(this=this, expression=other)
 763
 764    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]):
 765        return Bracket(
 766            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 767        )
 768
 769    def isin(
 770        self,
 771        *expressions: t.Any,
 772        query: t.Optional[ExpOrStr] = None,
 773        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 774        copy: bool = True,
 775        **opts,
 776    ) -> In:
 777        return In(
 778            this=maybe_copy(self, copy),
 779            expressions=[convert(e, copy=copy) for e in expressions],
 780            query=maybe_parse(query, copy=copy, **opts) if query else None,
 781            unnest=Unnest(
 782                expressions=[
 783                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 784                ]
 785            )
 786            if unnest
 787            else None,
 788        )
 789
 790    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 791        return Between(
 792            this=maybe_copy(self, copy),
 793            low=convert(low, copy=copy, **opts),
 794            high=convert(high, copy=copy, **opts),
 795        )
 796
 797    def is_(self, other: ExpOrStr) -> Is:
 798        return self._binop(Is, other)
 799
 800    def like(self, other: ExpOrStr) -> Like:
 801        return self._binop(Like, other)
 802
 803    def ilike(self, other: ExpOrStr) -> ILike:
 804        return self._binop(ILike, other)
 805
 806    def eq(self, other: t.Any) -> EQ:
 807        return self._binop(EQ, other)
 808
 809    def neq(self, other: t.Any) -> NEQ:
 810        return self._binop(NEQ, other)
 811
 812    def rlike(self, other: ExpOrStr) -> RegexpLike:
 813        return self._binop(RegexpLike, other)
 814
 815    def __lt__(self, other: t.Any) -> LT:
 816        return self._binop(LT, other)
 817
 818    def __le__(self, other: t.Any) -> LTE:
 819        return self._binop(LTE, other)
 820
 821    def __gt__(self, other: t.Any) -> GT:
 822        return self._binop(GT, other)
 823
 824    def __ge__(self, other: t.Any) -> GTE:
 825        return self._binop(GTE, other)
 826
 827    def __add__(self, other: t.Any) -> Add:
 828        return self._binop(Add, other)
 829
 830    def __radd__(self, other: t.Any) -> Add:
 831        return self._binop(Add, other, reverse=True)
 832
 833    def __sub__(self, other: t.Any) -> Sub:
 834        return self._binop(Sub, other)
 835
 836    def __rsub__(self, other: t.Any) -> Sub:
 837        return self._binop(Sub, other, reverse=True)
 838
 839    def __mul__(self, other: t.Any) -> Mul:
 840        return self._binop(Mul, other)
 841
 842    def __rmul__(self, other: t.Any) -> Mul:
 843        return self._binop(Mul, other, reverse=True)
 844
 845    def __truediv__(self, other: t.Any) -> Div:
 846        return self._binop(Div, other)
 847
 848    def __rtruediv__(self, other: t.Any) -> Div:
 849        return self._binop(Div, other, reverse=True)
 850
 851    def __floordiv__(self, other: t.Any) -> IntDiv:
 852        return self._binop(IntDiv, other)
 853
 854    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 855        return self._binop(IntDiv, other, reverse=True)
 856
 857    def __mod__(self, other: t.Any) -> Mod:
 858        return self._binop(Mod, other)
 859
 860    def __rmod__(self, other: t.Any) -> Mod:
 861        return self._binop(Mod, other, reverse=True)
 862
 863    def __pow__(self, other: t.Any) -> Pow:
 864        return self._binop(Pow, other)
 865
 866    def __rpow__(self, other: t.Any) -> Pow:
 867        return self._binop(Pow, other, reverse=True)
 868
 869    def __and__(self, other: t.Any) -> And:
 870        return self._binop(And, other)
 871
 872    def __rand__(self, other: t.Any) -> And:
 873        return self._binop(And, other, reverse=True)
 874
 875    def __or__(self, other: t.Any) -> Or:
 876        return self._binop(Or, other)
 877
 878    def __ror__(self, other: t.Any) -> Or:
 879        return self._binop(Or, other, reverse=True)
 880
 881    def __neg__(self) -> Neg:
 882        return Neg(this=_wrap(self.copy(), Binary))
 883
 884    def __invert__(self) -> Not:
 885        return not_(self.copy())
 886
 887
 888class Predicate(Condition):
 889    """Relationships like x = y, x > 1, x >= y."""
 890
 891
 892class DerivedTable(Expression):
 893    @property
 894    def selects(self) -> t.List[Expression]:
 895        return self.this.selects if isinstance(self.this, Subqueryable) else []
 896
 897    @property
 898    def named_selects(self) -> t.List[str]:
 899        return [select.output_name for select in self.selects]
 900
 901
 902class Unionable(Expression):
 903    def union(
 904        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 905    ) -> Unionable:
 906        """
 907        Builds a UNION expression.
 908
 909        Example:
 910            >>> import sqlglot
 911            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 912            'SELECT * FROM foo UNION SELECT * FROM bla'
 913
 914        Args:
 915            expression: the SQL code string.
 916                If an `Expression` instance is passed, it will be used as-is.
 917            distinct: set the DISTINCT flag if and only if this is true.
 918            dialect: the dialect used to parse the input expression.
 919            opts: other options to use to parse the input expressions.
 920
 921        Returns:
 922            The new Union expression.
 923        """
 924        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 925
 926    def intersect(
 927        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 928    ) -> Unionable:
 929        """
 930        Builds an INTERSECT expression.
 931
 932        Example:
 933            >>> import sqlglot
 934            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 935            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 936
 937        Args:
 938            expression: the SQL code string.
 939                If an `Expression` instance is passed, it will be used as-is.
 940            distinct: set the DISTINCT flag if and only if this is true.
 941            dialect: the dialect used to parse the input expression.
 942            opts: other options to use to parse the input expressions.
 943
 944        Returns:
 945            The new Intersect expression.
 946        """
 947        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 948
 949    def except_(
 950        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 951    ) -> Unionable:
 952        """
 953        Builds an EXCEPT expression.
 954
 955        Example:
 956            >>> import sqlglot
 957            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 958            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 959
 960        Args:
 961            expression: the SQL code string.
 962                If an `Expression` instance is passed, it will be used as-is.
 963            distinct: set the DISTINCT flag if and only if this is true.
 964            dialect: the dialect used to parse the input expression.
 965            opts: other options to use to parse the input expressions.
 966
 967        Returns:
 968            The new Except expression.
 969        """
 970        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 971
 972
 973class UDTF(DerivedTable, Unionable):
 974    @property
 975    def selects(self) -> t.List[Expression]:
 976        alias = self.args.get("alias")
 977        return alias.columns if alias else []
 978
 979
 980class Cache(Expression):
 981    arg_types = {
 982        "with": False,
 983        "this": True,
 984        "lazy": False,
 985        "options": False,
 986        "expression": False,
 987    }
 988
 989
 990class Uncache(Expression):
 991    arg_types = {"this": True, "exists": False}
 992
 993
 994class DDL(Expression):
 995    @property
 996    def ctes(self):
 997        with_ = self.args.get("with")
 998        if not with_:
 999            return []
1000        return with_.expressions
1001
1002    @property
1003    def named_selects(self) -> t.List[str]:
1004        if isinstance(self.expression, Subqueryable):
1005            return self.expression.named_selects
1006        return []
1007
1008    @property
1009    def selects(self) -> t.List[Expression]:
1010        if isinstance(self.expression, Subqueryable):
1011            return self.expression.selects
1012        return []
1013
1014
1015class Create(DDL):
1016    arg_types = {
1017        "with": False,
1018        "this": True,
1019        "kind": True,
1020        "expression": False,
1021        "exists": False,
1022        "properties": False,
1023        "replace": False,
1024        "unique": False,
1025        "indexes": False,
1026        "no_schema_binding": False,
1027        "begin": False,
1028        "clone": False,
1029    }
1030
1031
1032# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1033class Clone(Expression):
1034    arg_types = {
1035        "this": True,
1036        "when": False,
1037        "kind": False,
1038        "expression": False,
1039    }
1040
1041
1042class Describe(Expression):
1043    arg_types = {"this": True, "kind": False}
1044
1045
1046class Pragma(Expression):
1047    pass
1048
1049
1050class Set(Expression):
1051    arg_types = {"expressions": False, "unset": False, "tag": False}
1052
1053
1054class SetItem(Expression):
1055    arg_types = {
1056        "this": False,
1057        "expressions": False,
1058        "kind": False,
1059        "collate": False,  # MySQL SET NAMES statement
1060        "global": False,
1061    }
1062
1063
1064class Show(Expression):
1065    arg_types = {
1066        "this": True,
1067        "target": False,
1068        "offset": False,
1069        "limit": False,
1070        "like": False,
1071        "where": False,
1072        "db": False,
1073        "full": False,
1074        "mutex": False,
1075        "query": False,
1076        "channel": False,
1077        "global": False,
1078        "log": False,
1079        "position": False,
1080        "types": False,
1081    }
1082
1083
1084class UserDefinedFunction(Expression):
1085    arg_types = {"this": True, "expressions": False, "wrapped": False}
1086
1087
1088class CharacterSet(Expression):
1089    arg_types = {"this": True, "default": False}
1090
1091
1092class With(Expression):
1093    arg_types = {"expressions": True, "recursive": False}
1094
1095    @property
1096    def recursive(self) -> bool:
1097        return bool(self.args.get("recursive"))
1098
1099
1100class WithinGroup(Expression):
1101    arg_types = {"this": True, "expression": False}
1102
1103
1104class CTE(DerivedTable):
1105    arg_types = {"this": True, "alias": True}
1106
1107
1108class TableAlias(Expression):
1109    arg_types = {"this": False, "columns": False}
1110
1111    @property
1112    def columns(self):
1113        return self.args.get("columns") or []
1114
1115
1116class BitString(Condition):
1117    pass
1118
1119
1120class HexString(Condition):
1121    pass
1122
1123
1124class ByteString(Condition):
1125    pass
1126
1127
1128class RawString(Condition):
1129    pass
1130
1131
1132class Column(Condition):
1133    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1134
1135    @property
1136    def table(self) -> str:
1137        return self.text("table")
1138
1139    @property
1140    def db(self) -> str:
1141        return self.text("db")
1142
1143    @property
1144    def catalog(self) -> str:
1145        return self.text("catalog")
1146
1147    @property
1148    def output_name(self) -> str:
1149        return self.name
1150
1151    @property
1152    def parts(self) -> t.List[Identifier]:
1153        """Return the parts of a column in order catalog, db, table, name."""
1154        return [
1155            t.cast(Identifier, self.args[part])
1156            for part in ("catalog", "db", "table", "this")
1157            if self.args.get(part)
1158        ]
1159
1160    def to_dot(self) -> Dot:
1161        """Converts the column into a dot expression."""
1162        parts = self.parts
1163        parent = self.parent
1164
1165        while parent:
1166            if isinstance(parent, Dot):
1167                parts.append(parent.expression)
1168            parent = parent.parent
1169
1170        return Dot.build(parts)
1171
1172
1173class ColumnPosition(Expression):
1174    arg_types = {"this": False, "position": True}
1175
1176
1177class ColumnDef(Expression):
1178    arg_types = {
1179        "this": True,
1180        "kind": False,
1181        "constraints": False,
1182        "exists": False,
1183        "position": False,
1184    }
1185
1186    @property
1187    def constraints(self) -> t.List[ColumnConstraint]:
1188        return self.args.get("constraints") or []
1189
1190
1191class AlterColumn(Expression):
1192    arg_types = {
1193        "this": True,
1194        "dtype": False,
1195        "collate": False,
1196        "using": False,
1197        "default": False,
1198        "drop": False,
1199    }
1200
1201
1202class RenameTable(Expression):
1203    pass
1204
1205
1206class Comment(Expression):
1207    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1208
1209
1210class Comprehension(Expression):
1211    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1212
1213
1214# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1215class MergeTreeTTLAction(Expression):
1216    arg_types = {
1217        "this": True,
1218        "delete": False,
1219        "recompress": False,
1220        "to_disk": False,
1221        "to_volume": False,
1222    }
1223
1224
1225# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1226class MergeTreeTTL(Expression):
1227    arg_types = {
1228        "expressions": True,
1229        "where": False,
1230        "group": False,
1231        "aggregates": False,
1232    }
1233
1234
1235# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1236class IndexConstraintOption(Expression):
1237    arg_types = {
1238        "key_block_size": False,
1239        "using": False,
1240        "parser": False,
1241        "comment": False,
1242        "visible": False,
1243        "engine_attr": False,
1244        "secondary_engine_attr": False,
1245    }
1246
1247
1248class ColumnConstraint(Expression):
1249    arg_types = {"this": False, "kind": True}
1250
1251    @property
1252    def kind(self) -> ColumnConstraintKind:
1253        return self.args["kind"]
1254
1255
1256class ColumnConstraintKind(Expression):
1257    pass
1258
1259
1260class AutoIncrementColumnConstraint(ColumnConstraintKind):
1261    pass
1262
1263
1264class CaseSpecificColumnConstraint(ColumnConstraintKind):
1265    arg_types = {"not_": True}
1266
1267
1268class CharacterSetColumnConstraint(ColumnConstraintKind):
1269    arg_types = {"this": True}
1270
1271
1272class CheckColumnConstraint(ColumnConstraintKind):
1273    pass
1274
1275
1276class ClusteredColumnConstraint(ColumnConstraintKind):
1277    pass
1278
1279
1280class CollateColumnConstraint(ColumnConstraintKind):
1281    pass
1282
1283
1284class CommentColumnConstraint(ColumnConstraintKind):
1285    pass
1286
1287
1288class CompressColumnConstraint(ColumnConstraintKind):
1289    pass
1290
1291
1292class DateFormatColumnConstraint(ColumnConstraintKind):
1293    arg_types = {"this": True}
1294
1295
1296class DefaultColumnConstraint(ColumnConstraintKind):
1297    pass
1298
1299
1300class EncodeColumnConstraint(ColumnConstraintKind):
1301    pass
1302
1303
1304class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1305    # this: True -> ALWAYS, this: False -> BY DEFAULT
1306    arg_types = {
1307        "this": False,
1308        "expression": False,
1309        "on_null": False,
1310        "start": False,
1311        "increment": False,
1312        "minvalue": False,
1313        "maxvalue": False,
1314        "cycle": False,
1315    }
1316
1317
1318# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1319class IndexColumnConstraint(ColumnConstraintKind):
1320    arg_types = {"this": False, "schema": True, "kind": False, "type": False, "options": False}
1321
1322
1323class InlineLengthColumnConstraint(ColumnConstraintKind):
1324    pass
1325
1326
1327class NonClusteredColumnConstraint(ColumnConstraintKind):
1328    pass
1329
1330
1331class NotForReplicationColumnConstraint(ColumnConstraintKind):
1332    arg_types = {}
1333
1334
1335class NotNullColumnConstraint(ColumnConstraintKind):
1336    arg_types = {"allow_null": False}
1337
1338
1339# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1340class OnUpdateColumnConstraint(ColumnConstraintKind):
1341    pass
1342
1343
1344class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1345    arg_types = {"desc": False}
1346
1347
1348class TitleColumnConstraint(ColumnConstraintKind):
1349    pass
1350
1351
1352class UniqueColumnConstraint(ColumnConstraintKind):
1353    arg_types = {"this": False}
1354
1355
1356class UppercaseColumnConstraint(ColumnConstraintKind):
1357    arg_types: t.Dict[str, t.Any] = {}
1358
1359
1360class PathColumnConstraint(ColumnConstraintKind):
1361    pass
1362
1363
1364# computed column expression
1365# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1366class ComputedColumnConstraint(ColumnConstraintKind):
1367    arg_types = {"this": True, "persisted": False, "not_null": False}
1368
1369
1370class Constraint(Expression):
1371    arg_types = {"this": True, "expressions": True}
1372
1373
1374class Delete(Expression):
1375    arg_types = {
1376        "with": False,
1377        "this": False,
1378        "using": False,
1379        "where": False,
1380        "returning": False,
1381        "limit": False,
1382        "tables": False,  # Multiple-Table Syntax (MySQL)
1383    }
1384
1385    def delete(
1386        self,
1387        table: ExpOrStr,
1388        dialect: DialectType = None,
1389        copy: bool = True,
1390        **opts,
1391    ) -> Delete:
1392        """
1393        Create a DELETE expression or replace the table on an existing DELETE expression.
1394
1395        Example:
1396            >>> delete("tbl").sql()
1397            'DELETE FROM tbl'
1398
1399        Args:
1400            table: the table from which to delete.
1401            dialect: the dialect used to parse the input expression.
1402            copy: if `False`, modify this expression instance in-place.
1403            opts: other options to use to parse the input expressions.
1404
1405        Returns:
1406            Delete: the modified expression.
1407        """
1408        return _apply_builder(
1409            expression=table,
1410            instance=self,
1411            arg="this",
1412            dialect=dialect,
1413            into=Table,
1414            copy=copy,
1415            **opts,
1416        )
1417
1418    def where(
1419        self,
1420        *expressions: t.Optional[ExpOrStr],
1421        append: bool = True,
1422        dialect: DialectType = None,
1423        copy: bool = True,
1424        **opts,
1425    ) -> Delete:
1426        """
1427        Append to or set the WHERE expressions.
1428
1429        Example:
1430            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1431            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1432
1433        Args:
1434            *expressions: the SQL code strings to parse.
1435                If an `Expression` instance is passed, it will be used as-is.
1436                Multiple expressions are combined with an AND operator.
1437            append: if `True`, AND the new expressions to any existing expression.
1438                Otherwise, this resets the expression.
1439            dialect: the dialect used to parse the input expressions.
1440            copy: if `False`, modify this expression instance in-place.
1441            opts: other options to use to parse the input expressions.
1442
1443        Returns:
1444            Delete: the modified expression.
1445        """
1446        return _apply_conjunction_builder(
1447            *expressions,
1448            instance=self,
1449            arg="where",
1450            append=append,
1451            into=Where,
1452            dialect=dialect,
1453            copy=copy,
1454            **opts,
1455        )
1456
1457    def returning(
1458        self,
1459        expression: ExpOrStr,
1460        dialect: DialectType = None,
1461        copy: bool = True,
1462        **opts,
1463    ) -> Delete:
1464        """
1465        Set the RETURNING expression. Not supported by all dialects.
1466
1467        Example:
1468            >>> delete("tbl").returning("*", dialect="postgres").sql()
1469            'DELETE FROM tbl RETURNING *'
1470
1471        Args:
1472            expression: the SQL code strings to parse.
1473                If an `Expression` instance is passed, it will be used as-is.
1474            dialect: the dialect used to parse the input expressions.
1475            copy: if `False`, modify this expression instance in-place.
1476            opts: other options to use to parse the input expressions.
1477
1478        Returns:
1479            Delete: the modified expression.
1480        """
1481        return _apply_builder(
1482            expression=expression,
1483            instance=self,
1484            arg="returning",
1485            prefix="RETURNING",
1486            dialect=dialect,
1487            copy=copy,
1488            into=Returning,
1489            **opts,
1490        )
1491
1492
1493class Drop(Expression):
1494    arg_types = {
1495        "this": False,
1496        "kind": False,
1497        "exists": False,
1498        "temporary": False,
1499        "materialized": False,
1500        "cascade": False,
1501        "constraints": False,
1502        "purge": False,
1503    }
1504
1505
1506class Filter(Expression):
1507    arg_types = {"this": True, "expression": True}
1508
1509
1510class Check(Expression):
1511    pass
1512
1513
1514# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1515class Connect(Expression):
1516    arg_types = {"start": False, "connect": True}
1517
1518
1519class Prior(Expression):
1520    pass
1521
1522
1523class Directory(Expression):
1524    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1525    arg_types = {"this": True, "local": False, "row_format": False}
1526
1527
1528class ForeignKey(Expression):
1529    arg_types = {
1530        "expressions": True,
1531        "reference": False,
1532        "delete": False,
1533        "update": False,
1534    }
1535
1536
1537class PrimaryKey(Expression):
1538    arg_types = {"expressions": True, "options": False}
1539
1540
1541# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1542# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1543class Into(Expression):
1544    arg_types = {"this": True, "temporary": False, "unlogged": False}
1545
1546
1547class From(Expression):
1548    @property
1549    def name(self) -> str:
1550        return self.this.name
1551
1552    @property
1553    def alias_or_name(self) -> str:
1554        return self.this.alias_or_name
1555
1556
1557class Having(Expression):
1558    pass
1559
1560
1561class Hint(Expression):
1562    arg_types = {"expressions": True}
1563
1564
1565class JoinHint(Expression):
1566    arg_types = {"this": True, "expressions": True}
1567
1568
1569class Identifier(Expression):
1570    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1571
1572    @property
1573    def quoted(self) -> bool:
1574        return bool(self.args.get("quoted"))
1575
1576    @property
1577    def hashable_args(self) -> t.Any:
1578        return (self.this, self.quoted)
1579
1580    @property
1581    def output_name(self) -> str:
1582        return self.name
1583
1584
1585class Index(Expression):
1586    arg_types = {
1587        "this": False,
1588        "table": False,
1589        "using": False,
1590        "where": False,
1591        "columns": False,
1592        "unique": False,
1593        "primary": False,
1594        "amp": False,  # teradata
1595        "partition_by": False,  # teradata
1596    }
1597
1598
1599class Insert(DDL):
1600    arg_types = {
1601        "with": False,
1602        "this": True,
1603        "expression": False,
1604        "conflict": False,
1605        "returning": False,
1606        "overwrite": False,
1607        "exists": False,
1608        "partition": False,
1609        "alternative": False,
1610        "where": False,
1611        "ignore": False,
1612        "by_name": False,
1613    }
1614
1615    def with_(
1616        self,
1617        alias: ExpOrStr,
1618        as_: ExpOrStr,
1619        recursive: t.Optional[bool] = None,
1620        append: bool = True,
1621        dialect: DialectType = None,
1622        copy: bool = True,
1623        **opts,
1624    ) -> Insert:
1625        """
1626        Append to or set the common table expressions.
1627
1628        Example:
1629            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1630            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1631
1632        Args:
1633            alias: the SQL code string to parse as the table name.
1634                If an `Expression` instance is passed, this is used as-is.
1635            as_: the SQL code string to parse as the table expression.
1636                If an `Expression` instance is passed, it will be used as-is.
1637            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1638            append: if `True`, add to any existing expressions.
1639                Otherwise, this resets the expressions.
1640            dialect: the dialect used to parse the input expression.
1641            copy: if `False`, modify this expression instance in-place.
1642            opts: other options to use to parse the input expressions.
1643
1644        Returns:
1645            The modified expression.
1646        """
1647        return _apply_cte_builder(
1648            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1649        )
1650
1651
1652class OnConflict(Expression):
1653    arg_types = {
1654        "duplicate": False,
1655        "expressions": False,
1656        "nothing": False,
1657        "key": False,
1658        "constraint": False,
1659    }
1660
1661
1662class Returning(Expression):
1663    arg_types = {"expressions": True, "into": False}
1664
1665
1666# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1667class Introducer(Expression):
1668    arg_types = {"this": True, "expression": True}
1669
1670
1671# national char, like n'utf8'
1672class National(Expression):
1673    pass
1674
1675
1676class LoadData(Expression):
1677    arg_types = {
1678        "this": True,
1679        "local": False,
1680        "overwrite": False,
1681        "inpath": True,
1682        "partition": False,
1683        "input_format": False,
1684        "serde": False,
1685    }
1686
1687
1688class Partition(Expression):
1689    arg_types = {"expressions": True}
1690
1691
1692class Fetch(Expression):
1693    arg_types = {
1694        "direction": False,
1695        "count": False,
1696        "percent": False,
1697        "with_ties": False,
1698    }
1699
1700
1701class Group(Expression):
1702    arg_types = {
1703        "expressions": False,
1704        "grouping_sets": False,
1705        "cube": False,
1706        "rollup": False,
1707        "totals": False,
1708        "all": False,
1709    }
1710
1711
1712class Lambda(Expression):
1713    arg_types = {"this": True, "expressions": True}
1714
1715
1716class Limit(Expression):
1717    arg_types = {"this": False, "expression": True, "offset": False}
1718
1719
1720class Literal(Condition):
1721    arg_types = {"this": True, "is_string": True}
1722
1723    @property
1724    def hashable_args(self) -> t.Any:
1725        return (self.this, self.args.get("is_string"))
1726
1727    @classmethod
1728    def number(cls, number) -> Literal:
1729        return cls(this=str(number), is_string=False)
1730
1731    @classmethod
1732    def string(cls, string) -> Literal:
1733        return cls(this=str(string), is_string=True)
1734
1735    @property
1736    def output_name(self) -> str:
1737        return self.name
1738
1739
1740class Join(Expression):
1741    arg_types = {
1742        "this": True,
1743        "on": False,
1744        "side": False,
1745        "kind": False,
1746        "using": False,
1747        "method": False,
1748        "global": False,
1749        "hint": False,
1750    }
1751
1752    @property
1753    def method(self) -> str:
1754        return self.text("method").upper()
1755
1756    @property
1757    def kind(self) -> str:
1758        return self.text("kind").upper()
1759
1760    @property
1761    def side(self) -> str:
1762        return self.text("side").upper()
1763
1764    @property
1765    def hint(self) -> str:
1766        return self.text("hint").upper()
1767
1768    @property
1769    def alias_or_name(self) -> str:
1770        return self.this.alias_or_name
1771
1772    def on(
1773        self,
1774        *expressions: t.Optional[ExpOrStr],
1775        append: bool = True,
1776        dialect: DialectType = None,
1777        copy: bool = True,
1778        **opts,
1779    ) -> Join:
1780        """
1781        Append to or set the ON expressions.
1782
1783        Example:
1784            >>> import sqlglot
1785            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1786            'JOIN x ON y = 1'
1787
1788        Args:
1789            *expressions: the SQL code strings to parse.
1790                If an `Expression` instance is passed, it will be used as-is.
1791                Multiple expressions are combined with an AND operator.
1792            append: if `True`, AND the new expressions to any existing expression.
1793                Otherwise, this resets the expression.
1794            dialect: the dialect used to parse the input expressions.
1795            copy: if `False`, modify this expression instance in-place.
1796            opts: other options to use to parse the input expressions.
1797
1798        Returns:
1799            The modified Join expression.
1800        """
1801        join = _apply_conjunction_builder(
1802            *expressions,
1803            instance=self,
1804            arg="on",
1805            append=append,
1806            dialect=dialect,
1807            copy=copy,
1808            **opts,
1809        )
1810
1811        if join.kind == "CROSS":
1812            join.set("kind", None)
1813
1814        return join
1815
1816    def using(
1817        self,
1818        *expressions: t.Optional[ExpOrStr],
1819        append: bool = True,
1820        dialect: DialectType = None,
1821        copy: bool = True,
1822        **opts,
1823    ) -> Join:
1824        """
1825        Append to or set the USING expressions.
1826
1827        Example:
1828            >>> import sqlglot
1829            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1830            'JOIN x USING (foo, bla)'
1831
1832        Args:
1833            *expressions: the SQL code strings to parse.
1834                If an `Expression` instance is passed, it will be used as-is.
1835            append: if `True`, concatenate the new expressions to the existing "using" list.
1836                Otherwise, this resets the expression.
1837            dialect: the dialect used to parse the input expressions.
1838            copy: if `False`, modify this expression instance in-place.
1839            opts: other options to use to parse the input expressions.
1840
1841        Returns:
1842            The modified Join expression.
1843        """
1844        join = _apply_list_builder(
1845            *expressions,
1846            instance=self,
1847            arg="using",
1848            append=append,
1849            dialect=dialect,
1850            copy=copy,
1851            **opts,
1852        )
1853
1854        if join.kind == "CROSS":
1855            join.set("kind", None)
1856
1857        return join
1858
1859
1860class Lateral(UDTF):
1861    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1862
1863
1864class MatchRecognize(Expression):
1865    arg_types = {
1866        "partition_by": False,
1867        "order": False,
1868        "measures": False,
1869        "rows": False,
1870        "after": False,
1871        "pattern": False,
1872        "define": False,
1873        "alias": False,
1874    }
1875
1876
1877# Clickhouse FROM FINAL modifier
1878# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1879class Final(Expression):
1880    pass
1881
1882
1883class Offset(Expression):
1884    arg_types = {"this": False, "expression": True}
1885
1886
1887class Order(Expression):
1888    arg_types = {"this": False, "expressions": True}
1889
1890
1891# hive specific sorts
1892# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1893class Cluster(Order):
1894    pass
1895
1896
1897class Distribute(Order):
1898    pass
1899
1900
1901class Sort(Order):
1902    pass
1903
1904
1905class Ordered(Expression):
1906    arg_types = {"this": True, "desc": True, "nulls_first": True}
1907
1908
1909class Property(Expression):
1910    arg_types = {"this": True, "value": True}
1911
1912
1913class AlgorithmProperty(Property):
1914    arg_types = {"this": True}
1915
1916
1917class AutoIncrementProperty(Property):
1918    arg_types = {"this": True}
1919
1920
1921class BlockCompressionProperty(Property):
1922    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1923
1924
1925class CharacterSetProperty(Property):
1926    arg_types = {"this": True, "default": True}
1927
1928
1929class ChecksumProperty(Property):
1930    arg_types = {"on": False, "default": False}
1931
1932
1933class CollateProperty(Property):
1934    arg_types = {"this": True}
1935
1936
1937class CopyGrantsProperty(Property):
1938    arg_types = {}
1939
1940
1941class DataBlocksizeProperty(Property):
1942    arg_types = {
1943        "size": False,
1944        "units": False,
1945        "minimum": False,
1946        "maximum": False,
1947        "default": False,
1948    }
1949
1950
1951class DefinerProperty(Property):
1952    arg_types = {"this": True}
1953
1954
1955class DistKeyProperty(Property):
1956    arg_types = {"this": True}
1957
1958
1959class DistStyleProperty(Property):
1960    arg_types = {"this": True}
1961
1962
1963class EngineProperty(Property):
1964    arg_types = {"this": True}
1965
1966
1967class HeapProperty(Property):
1968    arg_types = {}
1969
1970
1971class ToTableProperty(Property):
1972    arg_types = {"this": True}
1973
1974
1975class ExecuteAsProperty(Property):
1976    arg_types = {"this": True}
1977
1978
1979class ExternalProperty(Property):
1980    arg_types = {"this": False}
1981
1982
1983class FallbackProperty(Property):
1984    arg_types = {"no": True, "protection": False}
1985
1986
1987class FileFormatProperty(Property):
1988    arg_types = {"this": True}
1989
1990
1991class FreespaceProperty(Property):
1992    arg_types = {"this": True, "percent": False}
1993
1994
1995class InputOutputFormat(Expression):
1996    arg_types = {"input_format": False, "output_format": False}
1997
1998
1999class IsolatedLoadingProperty(Property):
2000    arg_types = {
2001        "no": True,
2002        "concurrent": True,
2003        "for_all": True,
2004        "for_insert": True,
2005        "for_none": True,
2006    }
2007
2008
2009class JournalProperty(Property):
2010    arg_types = {
2011        "no": False,
2012        "dual": False,
2013        "before": False,
2014        "local": False,
2015        "after": False,
2016    }
2017
2018
2019class LanguageProperty(Property):
2020    arg_types = {"this": True}
2021
2022
2023# spark ddl
2024class ClusteredByProperty(Property):
2025    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2026
2027
2028class DictProperty(Property):
2029    arg_types = {"this": True, "kind": True, "settings": False}
2030
2031
2032class DictSubProperty(Property):
2033    pass
2034
2035
2036class DictRange(Property):
2037    arg_types = {"this": True, "min": True, "max": True}
2038
2039
2040# Clickhouse CREATE ... ON CLUSTER modifier
2041# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2042class OnCluster(Property):
2043    arg_types = {"this": True}
2044
2045
2046class LikeProperty(Property):
2047    arg_types = {"this": True, "expressions": False}
2048
2049
2050class LocationProperty(Property):
2051    arg_types = {"this": True}
2052
2053
2054class LockingProperty(Property):
2055    arg_types = {
2056        "this": False,
2057        "kind": True,
2058        "for_or_in": True,
2059        "lock_type": True,
2060        "override": False,
2061    }
2062
2063
2064class LogProperty(Property):
2065    arg_types = {"no": True}
2066
2067
2068class MaterializedProperty(Property):
2069    arg_types = {"this": False}
2070
2071
2072class MergeBlockRatioProperty(Property):
2073    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2074
2075
2076class NoPrimaryIndexProperty(Property):
2077    arg_types = {}
2078
2079
2080class OnProperty(Property):
2081    arg_types = {"this": True}
2082
2083
2084class OnCommitProperty(Property):
2085    arg_types = {"delete": False}
2086
2087
2088class PartitionedByProperty(Property):
2089    arg_types = {"this": True}
2090
2091
2092class ReturnsProperty(Property):
2093    arg_types = {"this": True, "is_table": False, "table": False}
2094
2095
2096class RowFormatProperty(Property):
2097    arg_types = {"this": True}
2098
2099
2100class RowFormatDelimitedProperty(Property):
2101    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2102    arg_types = {
2103        "fields": False,
2104        "escaped": False,
2105        "collection_items": False,
2106        "map_keys": False,
2107        "lines": False,
2108        "null": False,
2109        "serde": False,
2110    }
2111
2112
2113class RowFormatSerdeProperty(Property):
2114    arg_types = {"this": True, "serde_properties": False}
2115
2116
2117# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2118class QueryTransform(Expression):
2119    arg_types = {
2120        "expressions": True,
2121        "command_script": True,
2122        "schema": False,
2123        "row_format_before": False,
2124        "record_writer": False,
2125        "row_format_after": False,
2126        "record_reader": False,
2127    }
2128
2129
2130class SchemaCommentProperty(Property):
2131    arg_types = {"this": True}
2132
2133
2134class SerdeProperties(Property):
2135    arg_types = {"expressions": True}
2136
2137
2138class SetProperty(Property):
2139    arg_types = {"multi": True}
2140
2141
2142class SettingsProperty(Property):
2143    arg_types = {"expressions": True}
2144
2145
2146class SortKeyProperty(Property):
2147    arg_types = {"this": True, "compound": False}
2148
2149
2150class SqlSecurityProperty(Property):
2151    arg_types = {"definer": True}
2152
2153
2154class StabilityProperty(Property):
2155    arg_types = {"this": True}
2156
2157
2158class TemporaryProperty(Property):
2159    arg_types = {}
2160
2161
2162class TransientProperty(Property):
2163    arg_types = {"this": False}
2164
2165
2166class VolatileProperty(Property):
2167    arg_types = {"this": False}
2168
2169
2170class WithDataProperty(Property):
2171    arg_types = {"no": True, "statistics": False}
2172
2173
2174class WithJournalTableProperty(Property):
2175    arg_types = {"this": True}
2176
2177
2178class Properties(Expression):
2179    arg_types = {"expressions": True}
2180
2181    NAME_TO_PROPERTY = {
2182        "ALGORITHM": AlgorithmProperty,
2183        "AUTO_INCREMENT": AutoIncrementProperty,
2184        "CHARACTER SET": CharacterSetProperty,
2185        "CLUSTERED_BY": ClusteredByProperty,
2186        "COLLATE": CollateProperty,
2187        "COMMENT": SchemaCommentProperty,
2188        "DEFINER": DefinerProperty,
2189        "DISTKEY": DistKeyProperty,
2190        "DISTSTYLE": DistStyleProperty,
2191        "ENGINE": EngineProperty,
2192        "EXECUTE AS": ExecuteAsProperty,
2193        "FORMAT": FileFormatProperty,
2194        "LANGUAGE": LanguageProperty,
2195        "LOCATION": LocationProperty,
2196        "PARTITIONED_BY": PartitionedByProperty,
2197        "RETURNS": ReturnsProperty,
2198        "ROW_FORMAT": RowFormatProperty,
2199        "SORTKEY": SortKeyProperty,
2200    }
2201
2202    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2203
2204    # CREATE property locations
2205    # Form: schema specified
2206    #   create [POST_CREATE]
2207    #     table a [POST_NAME]
2208    #     (b int) [POST_SCHEMA]
2209    #     with ([POST_WITH])
2210    #     index (b) [POST_INDEX]
2211    #
2212    # Form: alias selection
2213    #   create [POST_CREATE]
2214    #     table a [POST_NAME]
2215    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2216    #     index (c) [POST_INDEX]
2217    class Location(AutoName):
2218        POST_CREATE = auto()
2219        POST_NAME = auto()
2220        POST_SCHEMA = auto()
2221        POST_WITH = auto()
2222        POST_ALIAS = auto()
2223        POST_EXPRESSION = auto()
2224        POST_INDEX = auto()
2225        UNSUPPORTED = auto()
2226
2227    @classmethod
2228    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2229        expressions = []
2230        for key, value in properties_dict.items():
2231            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2232            if property_cls:
2233                expressions.append(property_cls(this=convert(value)))
2234            else:
2235                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2236
2237        return cls(expressions=expressions)
2238
2239
2240class Qualify(Expression):
2241    pass
2242
2243
2244# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2245class Return(Expression):
2246    pass
2247
2248
2249class Reference(Expression):
2250    arg_types = {"this": True, "expressions": False, "options": False}
2251
2252
2253class Tuple(Expression):
2254    arg_types = {"expressions": False}
2255
2256    def isin(
2257        self,
2258        *expressions: t.Any,
2259        query: t.Optional[ExpOrStr] = None,
2260        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2261        copy: bool = True,
2262        **opts,
2263    ) -> In:
2264        return In(
2265            this=maybe_copy(self, copy),
2266            expressions=[convert(e, copy=copy) for e in expressions],
2267            query=maybe_parse(query, copy=copy, **opts) if query else None,
2268            unnest=Unnest(
2269                expressions=[
2270                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2271                ]
2272            )
2273            if unnest
2274            else None,
2275        )
2276
2277
2278class Subqueryable(Unionable):
2279    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2280        """
2281        Convert this expression to an aliased expression that can be used as a Subquery.
2282
2283        Example:
2284            >>> subquery = Select().select("x").from_("tbl").subquery()
2285            >>> Select().select("x").from_(subquery).sql()
2286            'SELECT x FROM (SELECT x FROM tbl)'
2287
2288        Args:
2289            alias (str | Identifier): an optional alias for the subquery
2290            copy (bool): if `False`, modify this expression instance in-place.
2291
2292        Returns:
2293            Alias: the subquery
2294        """
2295        instance = maybe_copy(self, copy)
2296        if not isinstance(alias, Expression):
2297            alias = TableAlias(this=to_identifier(alias)) if alias else None
2298
2299        return Subquery(this=instance, alias=alias)
2300
2301    def limit(
2302        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2303    ) -> Select:
2304        raise NotImplementedError
2305
2306    @property
2307    def ctes(self):
2308        with_ = self.args.get("with")
2309        if not with_:
2310            return []
2311        return with_.expressions
2312
2313    @property
2314    def selects(self) -> t.List[Expression]:
2315        raise NotImplementedError("Subqueryable objects must implement `selects`")
2316
2317    @property
2318    def named_selects(self) -> t.List[str]:
2319        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2320
2321    def select(
2322        self,
2323        *expressions: t.Optional[ExpOrStr],
2324        append: bool = True,
2325        dialect: DialectType = None,
2326        copy: bool = True,
2327        **opts,
2328    ) -> Subqueryable:
2329        raise NotImplementedError("Subqueryable objects must implement `select`")
2330
2331    def with_(
2332        self,
2333        alias: ExpOrStr,
2334        as_: ExpOrStr,
2335        recursive: t.Optional[bool] = None,
2336        append: bool = True,
2337        dialect: DialectType = None,
2338        copy: bool = True,
2339        **opts,
2340    ) -> Subqueryable:
2341        """
2342        Append to or set the common table expressions.
2343
2344        Example:
2345            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2346            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2347
2348        Args:
2349            alias: the SQL code string to parse as the table name.
2350                If an `Expression` instance is passed, this is used as-is.
2351            as_: the SQL code string to parse as the table expression.
2352                If an `Expression` instance is passed, it will be used as-is.
2353            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2354            append: if `True`, add to any existing expressions.
2355                Otherwise, this resets the expressions.
2356            dialect: the dialect used to parse the input expression.
2357            copy: if `False`, modify this expression instance in-place.
2358            opts: other options to use to parse the input expressions.
2359
2360        Returns:
2361            The modified expression.
2362        """
2363        return _apply_cte_builder(
2364            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2365        )
2366
2367
2368QUERY_MODIFIERS = {
2369    "match": False,
2370    "laterals": False,
2371    "joins": False,
2372    "connect": False,
2373    "pivots": False,
2374    "where": False,
2375    "group": False,
2376    "having": False,
2377    "qualify": False,
2378    "windows": False,
2379    "distribute": False,
2380    "sort": False,
2381    "cluster": False,
2382    "order": False,
2383    "limit": False,
2384    "offset": False,
2385    "locks": False,
2386    "sample": False,
2387    "settings": False,
2388    "format": False,
2389}
2390
2391
2392# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2393class WithTableHint(Expression):
2394    arg_types = {"expressions": True}
2395
2396
2397# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2398class IndexTableHint(Expression):
2399    arg_types = {"this": True, "expressions": False, "target": False}
2400
2401
2402class Table(Expression):
2403    arg_types = {
2404        "this": True,
2405        "alias": False,
2406        "db": False,
2407        "catalog": False,
2408        "laterals": False,
2409        "joins": False,
2410        "pivots": False,
2411        "hints": False,
2412        "system_time": False,
2413    }
2414
2415    @property
2416    def name(self) -> str:
2417        if isinstance(self.this, Func):
2418            return ""
2419        return self.this.name
2420
2421    @property
2422    def db(self) -> str:
2423        return self.text("db")
2424
2425    @property
2426    def catalog(self) -> str:
2427        return self.text("catalog")
2428
2429    @property
2430    def selects(self) -> t.List[Expression]:
2431        return []
2432
2433    @property
2434    def named_selects(self) -> t.List[str]:
2435        return []
2436
2437    @property
2438    def parts(self) -> t.List[Identifier]:
2439        """Return the parts of a table in order catalog, db, table."""
2440        parts: t.List[Identifier] = []
2441
2442        for arg in ("catalog", "db", "this"):
2443            part = self.args.get(arg)
2444
2445            if isinstance(part, Identifier):
2446                parts.append(part)
2447            elif isinstance(part, Dot):
2448                parts.extend(part.flatten())
2449
2450        return parts
2451
2452
2453# See the TSQL "Querying data in a system-versioned temporal table" page
2454class SystemTime(Expression):
2455    arg_types = {
2456        "this": False,
2457        "expression": False,
2458        "kind": True,
2459    }
2460
2461
2462class Union(Subqueryable):
2463    arg_types = {
2464        "with": False,
2465        "this": True,
2466        "expression": True,
2467        "distinct": False,
2468        "by_name": False,
2469        **QUERY_MODIFIERS,
2470    }
2471
2472    def limit(
2473        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2474    ) -> Select:
2475        """
2476        Set the LIMIT expression.
2477
2478        Example:
2479            >>> select("1").union(select("1")).limit(1).sql()
2480            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2481
2482        Args:
2483            expression: the SQL code string to parse.
2484                This can also be an integer.
2485                If a `Limit` instance is passed, this is used as-is.
2486                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2487            dialect: the dialect used to parse the input expression.
2488            copy: if `False`, modify this expression instance in-place.
2489            opts: other options to use to parse the input expressions.
2490
2491        Returns:
2492            The limited subqueryable.
2493        """
2494        return (
2495            select("*")
2496            .from_(self.subquery(alias="_l_0", copy=copy))
2497            .limit(expression, dialect=dialect, copy=False, **opts)
2498        )
2499
2500    def select(
2501        self,
2502        *expressions: t.Optional[ExpOrStr],
2503        append: bool = True,
2504        dialect: DialectType = None,
2505        copy: bool = True,
2506        **opts,
2507    ) -> Union:
2508        """Append to or set the SELECT of the union recursively.
2509
2510        Example:
2511            >>> from sqlglot import parse_one
2512            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2513            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2514
2515        Args:
2516            *expressions: the SQL code strings to parse.
2517                If an `Expression` instance is passed, it will be used as-is.
2518            append: if `True`, add to any existing expressions.
2519                Otherwise, this resets the expressions.
2520            dialect: the dialect used to parse the input expressions.
2521            copy: if `False`, modify this expression instance in-place.
2522            opts: other options to use to parse the input expressions.
2523
2524        Returns:
2525            Union: the modified expression.
2526        """
2527        this = self.copy() if copy else self
2528        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2529        this.expression.unnest().select(
2530            *expressions, append=append, dialect=dialect, copy=False, **opts
2531        )
2532        return this
2533
2534    @property
2535    def named_selects(self) -> t.List[str]:
2536        return self.this.unnest().named_selects
2537
2538    @property
2539    def is_star(self) -> bool:
2540        return self.this.is_star or self.expression.is_star
2541
2542    @property
2543    def selects(self) -> t.List[Expression]:
2544        return self.this.unnest().selects
2545
2546    @property
2547    def left(self):
2548        return self.this
2549
2550    @property
2551    def right(self):
2552        return self.expression
2553
2554
2555class Except(Union):
2556    pass
2557
2558
2559class Intersect(Union):
2560    pass
2561
2562
2563class Unnest(UDTF):
2564    arg_types = {
2565        "expressions": True,
2566        "ordinality": False,
2567        "alias": False,
2568        "offset": False,
2569    }
2570
2571
2572class Update(Expression):
2573    arg_types = {
2574        "with": False,
2575        "this": False,
2576        "expressions": True,
2577        "from": False,
2578        "where": False,
2579        "returning": False,
2580        "limit": False,
2581    }
2582
2583
2584class Values(UDTF):
2585    arg_types = {
2586        "expressions": True,
2587        "ordinality": False,
2588        "alias": False,
2589    }
2590
2591
2592class Var(Expression):
2593    pass
2594
2595
2596class Schema(Expression):
2597    arg_types = {"this": False, "expressions": False}
2598
2599
2600# https://dev.mysql.com/doc/refman/8.0/en/select.html
2601# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2602class Lock(Expression):
2603    arg_types = {"update": True, "expressions": False, "wait": False}
2604
2605
2606class Select(Subqueryable):
2607    arg_types = {
2608        "with": False,
2609        "kind": False,
2610        "expressions": False,
2611        "hint": False,
2612        "distinct": False,
2613        "into": False,
2614        "from": False,
2615        **QUERY_MODIFIERS,
2616    }
2617
2618    def from_(
2619        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2620    ) -> Select:
2621        """
2622        Set the FROM expression.
2623
2624        Example:
2625            >>> Select().from_("tbl").select("x").sql()
2626            'SELECT x FROM tbl'
2627
2628        Args:
2629            expression : the SQL code strings to parse.
2630                If a `From` instance is passed, this is used as-is.
2631                If another `Expression` instance is passed, it will be wrapped in a `From`.
2632            dialect: the dialect used to parse the input expression.
2633            copy: if `False`, modify this expression instance in-place.
2634            opts: other options to use to parse the input expressions.
2635
2636        Returns:
2637            The modified Select expression.
2638        """
2639        return _apply_builder(
2640            expression=expression,
2641            instance=self,
2642            arg="from",
2643            into=From,
2644            prefix="FROM",
2645            dialect=dialect,
2646            copy=copy,
2647            **opts,
2648        )
2649
2650    def group_by(
2651        self,
2652        *expressions: t.Optional[ExpOrStr],
2653        append: bool = True,
2654        dialect: DialectType = None,
2655        copy: bool = True,
2656        **opts,
2657    ) -> Select:
2658        """
2659        Set the GROUP BY expression.
2660
2661        Example:
2662            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2663            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2664
2665        Args:
2666            *expressions: the SQL code strings to parse.
2667                If a `Group` instance is passed, this is used as-is.
2668                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2669                If nothing is passed in then a group by is not applied to the expression
2670            append: if `True`, add to any existing expressions.
2671                Otherwise, this flattens all the `Group` expression into a single expression.
2672            dialect: the dialect used to parse the input expression.
2673            copy: if `False`, modify this expression instance in-place.
2674            opts: other options to use to parse the input expressions.
2675
2676        Returns:
2677            The modified Select expression.
2678        """
2679        if not expressions:
2680            return self if not copy else self.copy()
2681
2682        return _apply_child_list_builder(
2683            *expressions,
2684            instance=self,
2685            arg="group",
2686            append=append,
2687            copy=copy,
2688            prefix="GROUP BY",
2689            into=Group,
2690            dialect=dialect,
2691            **opts,
2692        )
2693
2694    def order_by(
2695        self,
2696        *expressions: t.Optional[ExpOrStr],
2697        append: bool = True,
2698        dialect: DialectType = None,
2699        copy: bool = True,
2700        **opts,
2701    ) -> Select:
2702        """
2703        Set the ORDER BY expression.
2704
2705        Example:
2706            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2707            'SELECT x FROM tbl ORDER BY x DESC'
2708
2709        Args:
2710            *expressions: the SQL code strings to parse.
2711                If a `Group` instance is passed, this is used as-is.
2712                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2713            append: if `True`, add to any existing expressions.
2714                Otherwise, this flattens all the `Order` expression into a single expression.
2715            dialect: the dialect used to parse the input expression.
2716            copy: if `False`, modify this expression instance in-place.
2717            opts: other options to use to parse the input expressions.
2718
2719        Returns:
2720            The modified Select expression.
2721        """
2722        return _apply_child_list_builder(
2723            *expressions,
2724            instance=self,
2725            arg="order",
2726            append=append,
2727            copy=copy,
2728            prefix="ORDER BY",
2729            into=Order,
2730            dialect=dialect,
2731            **opts,
2732        )
2733
2734    def sort_by(
2735        self,
2736        *expressions: t.Optional[ExpOrStr],
2737        append: bool = True,
2738        dialect: DialectType = None,
2739        copy: bool = True,
2740        **opts,
2741    ) -> Select:
2742        """
2743        Set the SORT BY expression.
2744
2745        Example:
2746            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2747            'SELECT x FROM tbl SORT BY x DESC'
2748
2749        Args:
2750            *expressions: the SQL code strings to parse.
2751                If a `Group` instance is passed, this is used as-is.
2752                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2753            append: if `True`, add to any existing expressions.
2754                Otherwise, this flattens all the `Order` expression into a single expression.
2755            dialect: the dialect used to parse the input expression.
2756            copy: if `False`, modify this expression instance in-place.
2757            opts: other options to use to parse the input expressions.
2758
2759        Returns:
2760            The modified Select expression.
2761        """
2762        return _apply_child_list_builder(
2763            *expressions,
2764            instance=self,
2765            arg="sort",
2766            append=append,
2767            copy=copy,
2768            prefix="SORT BY",
2769            into=Sort,
2770            dialect=dialect,
2771            **opts,
2772        )
2773
2774    def cluster_by(
2775        self,
2776        *expressions: t.Optional[ExpOrStr],
2777        append: bool = True,
2778        dialect: DialectType = None,
2779        copy: bool = True,
2780        **opts,
2781    ) -> Select:
2782        """
2783        Set the CLUSTER BY expression.
2784
2785        Example:
2786            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2787            'SELECT x FROM tbl CLUSTER BY x DESC'
2788
2789        Args:
2790            *expressions: the SQL code strings to parse.
2791                If a `Group` instance is passed, this is used as-is.
2792                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2793            append: if `True`, add to any existing expressions.
2794                Otherwise, this flattens all the `Order` expression into a single expression.
2795            dialect: the dialect used to parse the input expression.
2796            copy: if `False`, modify this expression instance in-place.
2797            opts: other options to use to parse the input expressions.
2798
2799        Returns:
2800            The modified Select expression.
2801        """
2802        return _apply_child_list_builder(
2803            *expressions,
2804            instance=self,
2805            arg="cluster",
2806            append=append,
2807            copy=copy,
2808            prefix="CLUSTER BY",
2809            into=Cluster,
2810            dialect=dialect,
2811            **opts,
2812        )
2813
2814    def limit(
2815        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2816    ) -> Select:
2817        """
2818        Set the LIMIT expression.
2819
2820        Example:
2821            >>> Select().from_("tbl").select("x").limit(10).sql()
2822            'SELECT x FROM tbl LIMIT 10'
2823
2824        Args:
2825            expression: the SQL code string to parse.
2826                This can also be an integer.
2827                If a `Limit` instance is passed, this is used as-is.
2828                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2829            dialect: the dialect used to parse the input expression.
2830            copy: if `False`, modify this expression instance in-place.
2831            opts: other options to use to parse the input expressions.
2832
2833        Returns:
2834            Select: the modified expression.
2835        """
2836        return _apply_builder(
2837            expression=expression,
2838            instance=self,
2839            arg="limit",
2840            into=Limit,
2841            prefix="LIMIT",
2842            dialect=dialect,
2843            copy=copy,
2844            **opts,
2845        )
2846
2847    def offset(
2848        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2849    ) -> Select:
2850        """
2851        Set the OFFSET expression.
2852
2853        Example:
2854            >>> Select().from_("tbl").select("x").offset(10).sql()
2855            'SELECT x FROM tbl OFFSET 10'
2856
2857        Args:
2858            expression: the SQL code string to parse.
2859                This can also be an integer.
2860                If a `Offset` instance is passed, this is used as-is.
2861                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2862            dialect: the dialect used to parse the input expression.
2863            copy: if `False`, modify this expression instance in-place.
2864            opts: other options to use to parse the input expressions.
2865
2866        Returns:
2867            The modified Select expression.
2868        """
2869        return _apply_builder(
2870            expression=expression,
2871            instance=self,
2872            arg="offset",
2873            into=Offset,
2874            prefix="OFFSET",
2875            dialect=dialect,
2876            copy=copy,
2877            **opts,
2878        )
2879
2880    def select(
2881        self,
2882        *expressions: t.Optional[ExpOrStr],
2883        append: bool = True,
2884        dialect: DialectType = None,
2885        copy: bool = True,
2886        **opts,
2887    ) -> Select:
2888        """
2889        Append to or set the SELECT expressions.
2890
2891        Example:
2892            >>> Select().select("x", "y").sql()
2893            'SELECT x, y'
2894
2895        Args:
2896            *expressions: the SQL code strings to parse.
2897                If an `Expression` instance is passed, it will be used as-is.
2898            append: if `True`, add to any existing expressions.
2899                Otherwise, this resets the expressions.
2900            dialect: the dialect used to parse the input expressions.
2901            copy: if `False`, modify this expression instance in-place.
2902            opts: other options to use to parse the input expressions.
2903
2904        Returns:
2905            The modified Select expression.
2906        """
2907        return _apply_list_builder(
2908            *expressions,
2909            instance=self,
2910            arg="expressions",
2911            append=append,
2912            dialect=dialect,
2913            copy=copy,
2914            **opts,
2915        )
2916
2917    def lateral(
2918        self,
2919        *expressions: t.Optional[ExpOrStr],
2920        append: bool = True,
2921        dialect: DialectType = None,
2922        copy: bool = True,
2923        **opts,
2924    ) -> Select:
2925        """
2926        Append to or set the LATERAL expressions.
2927
2928        Example:
2929            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2930            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2931
2932        Args:
2933            *expressions: the SQL code strings to parse.
2934                If an `Expression` instance is passed, it will be used as-is.
2935            append: if `True`, add to any existing expressions.
2936                Otherwise, this resets the expressions.
2937            dialect: the dialect used to parse the input expressions.
2938            copy: if `False`, modify this expression instance in-place.
2939            opts: other options to use to parse the input expressions.
2940
2941        Returns:
2942            The modified Select expression.
2943        """
2944        return _apply_list_builder(
2945            *expressions,
2946            instance=self,
2947            arg="laterals",
2948            append=append,
2949            into=Lateral,
2950            prefix="LATERAL VIEW",
2951            dialect=dialect,
2952            copy=copy,
2953            **opts,
2954        )
2955
2956    def join(
2957        self,
2958        expression: ExpOrStr,
2959        on: t.Optional[ExpOrStr] = None,
2960        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
2961        append: bool = True,
2962        join_type: t.Optional[str] = None,
2963        join_alias: t.Optional[Identifier | str] = None,
2964        dialect: DialectType = None,
2965        copy: bool = True,
2966        **opts,
2967    ) -> Select:
2968        """
2969        Append to or set the JOIN expressions.
2970
2971        Example:
2972            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2973            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2974
2975            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2976            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2977
2978            Use `join_type` to change the type of join:
2979
2980            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2981            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2982
2983        Args:
2984            expression: the SQL code string to parse.
2985                If an `Expression` instance is passed, it will be used as-is.
2986            on: optionally specify the join "on" criteria as a SQL string.
2987                If an `Expression` instance is passed, it will be used as-is.
2988            using: optionally specify the join "using" criteria as a SQL string.
2989                If an `Expression` instance is passed, it will be used as-is.
2990            append: if `True`, add to any existing expressions.
2991                Otherwise, this resets the expressions.
2992            join_type: if set, alter the parsed join type.
2993            join_alias: an optional alias for the joined source.
2994            dialect: the dialect used to parse the input expressions.
2995            copy: if `False`, modify this expression instance in-place.
2996            opts: other options to use to parse the input expressions.
2997
2998        Returns:
2999            Select: the modified expression.
3000        """
3001        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3002
3003        try:
3004            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3005        except ParseError:
3006            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3007
3008        join = expression if isinstance(expression, Join) else Join(this=expression)
3009
3010        if isinstance(join.this, Select):
3011            join.this.replace(join.this.subquery())
3012
3013        if join_type:
3014            method: t.Optional[Token]
3015            side: t.Optional[Token]
3016            kind: t.Optional[Token]
3017
3018            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3019
3020            if method:
3021                join.set("method", method.text)
3022            if side:
3023                join.set("side", side.text)
3024            if kind:
3025                join.set("kind", kind.text)
3026
3027        if on:
3028            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3029            join.set("on", on)
3030
3031        if using:
3032            join = _apply_list_builder(
3033                *ensure_list(using),
3034                instance=join,
3035                arg="using",
3036                append=append,
3037                copy=copy,
3038                into=Identifier,
3039                **opts,
3040            )
3041
3042        if join_alias:
3043            join.set("this", alias_(join.this, join_alias, table=True))
3044
3045        return _apply_list_builder(
3046            join,
3047            instance=self,
3048            arg="joins",
3049            append=append,
3050            copy=copy,
3051            **opts,
3052        )
3053
3054    def where(
3055        self,
3056        *expressions: t.Optional[ExpOrStr],
3057        append: bool = True,
3058        dialect: DialectType = None,
3059        copy: bool = True,
3060        **opts,
3061    ) -> Select:
3062        """
3063        Append to or set the WHERE expressions.
3064
3065        Example:
3066            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3067            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3068
3069        Args:
3070            *expressions: the SQL code strings to parse.
3071                If an `Expression` instance is passed, it will be used as-is.
3072                Multiple expressions are combined with an AND operator.
3073            append: if `True`, AND the new expressions to any existing expression.
3074                Otherwise, this resets the expression.
3075            dialect: the dialect used to parse the input expressions.
3076            copy: if `False`, modify this expression instance in-place.
3077            opts: other options to use to parse the input expressions.
3078
3079        Returns:
3080            Select: the modified expression.
3081        """
3082        return _apply_conjunction_builder(
3083            *expressions,
3084            instance=self,
3085            arg="where",
3086            append=append,
3087            into=Where,
3088            dialect=dialect,
3089            copy=copy,
3090            **opts,
3091        )
3092
3093    def having(
3094        self,
3095        *expressions: t.Optional[ExpOrStr],
3096        append: bool = True,
3097        dialect: DialectType = None,
3098        copy: bool = True,
3099        **opts,
3100    ) -> Select:
3101        """
3102        Append to or set the HAVING expressions.
3103
3104        Example:
3105            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3106            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3107
3108        Args:
3109            *expressions: the SQL code strings to parse.
3110                If an `Expression` instance is passed, it will be used as-is.
3111                Multiple expressions are combined with an AND operator.
3112            append: if `True`, AND the new expressions to any existing expression.
3113                Otherwise, this resets the expression.
3114            dialect: the dialect used to parse the input expressions.
3115            copy: if `False`, modify this expression instance in-place.
3116            opts: other options to use to parse the input expressions.
3117
3118        Returns:
3119            The modified Select expression.
3120        """
3121        return _apply_conjunction_builder(
3122            *expressions,
3123            instance=self,
3124            arg="having",
3125            append=append,
3126            into=Having,
3127            dialect=dialect,
3128            copy=copy,
3129            **opts,
3130        )
3131
3132    def window(
3133        self,
3134        *expressions: t.Optional[ExpOrStr],
3135        append: bool = True,
3136        dialect: DialectType = None,
3137        copy: bool = True,
3138        **opts,
3139    ) -> Select:
3140        return _apply_list_builder(
3141            *expressions,
3142            instance=self,
3143            arg="windows",
3144            append=append,
3145            into=Window,
3146            dialect=dialect,
3147            copy=copy,
3148            **opts,
3149        )
3150
3151    def qualify(
3152        self,
3153        *expressions: t.Optional[ExpOrStr],
3154        append: bool = True,
3155        dialect: DialectType = None,
3156        copy: bool = True,
3157        **opts,
3158    ) -> Select:
3159        return _apply_conjunction_builder(
3160            *expressions,
3161            instance=self,
3162            arg="qualify",
3163            append=append,
3164            into=Qualify,
3165            dialect=dialect,
3166            copy=copy,
3167            **opts,
3168        )
3169
3170    def distinct(
3171        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3172    ) -> Select:
3173        """
3174        Set the OFFSET expression.
3175
3176        Example:
3177            >>> Select().from_("tbl").select("x").distinct().sql()
3178            'SELECT DISTINCT x FROM tbl'
3179
3180        Args:
3181            ons: the expressions to distinct on
3182            distinct: whether the Select should be distinct
3183            copy: if `False`, modify this expression instance in-place.
3184
3185        Returns:
3186            Select: the modified expression.
3187        """
3188        instance = maybe_copy(self, copy)
3189        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3190        instance.set("distinct", Distinct(on=on) if distinct else None)
3191        return instance
3192
3193    def ctas(
3194        self,
3195        table: ExpOrStr,
3196        properties: t.Optional[t.Dict] = None,
3197        dialect: DialectType = None,
3198        copy: bool = True,
3199        **opts,
3200    ) -> Create:
3201        """
3202        Convert this expression to a CREATE TABLE AS statement.
3203
3204        Example:
3205            >>> Select().select("*").from_("tbl").ctas("x").sql()
3206            'CREATE TABLE x AS SELECT * FROM tbl'
3207
3208        Args:
3209            table: the SQL code string to parse as the table name.
3210                If another `Expression` instance is passed, it will be used as-is.
3211            properties: an optional mapping of table properties
3212            dialect: the dialect used to parse the input table.
3213            copy: if `False`, modify this expression instance in-place.
3214            opts: other options to use to parse the input table.
3215
3216        Returns:
3217            The new Create expression.
3218        """
3219        instance = maybe_copy(self, copy)
3220        table_expression = maybe_parse(
3221            table,
3222            into=Table,
3223            dialect=dialect,
3224            **opts,
3225        )
3226        properties_expression = None
3227        if properties:
3228            properties_expression = Properties.from_dict(properties)
3229
3230        return Create(
3231            this=table_expression,
3232            kind="table",
3233            expression=instance,
3234            properties=properties_expression,
3235        )
3236
3237    def lock(self, update: bool = True, copy: bool = True) -> Select:
3238        """
3239        Set the locking read mode for this expression.
3240
3241        Examples:
3242            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3243            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3244
3245            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3246            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3247
3248        Args:
3249            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3250            copy: if `False`, modify this expression instance in-place.
3251
3252        Returns:
3253            The modified expression.
3254        """
3255        inst = maybe_copy(self, copy)
3256        inst.set("locks", [Lock(update=update)])
3257
3258        return inst
3259
3260    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3261        """
3262        Set hints for this expression.
3263
3264        Examples:
3265            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3266            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3267
3268        Args:
3269            hints: The SQL code strings to parse as the hints.
3270                If an `Expression` instance is passed, it will be used as-is.
3271            dialect: The dialect used to parse the hints.
3272            copy: If `False`, modify this expression instance in-place.
3273
3274        Returns:
3275            The modified expression.
3276        """
3277        inst = maybe_copy(self, copy)
3278        inst.set(
3279            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3280        )
3281
3282        return inst
3283
3284    @property
3285    def named_selects(self) -> t.List[str]:
3286        return [e.output_name for e in self.expressions if e.alias_or_name]
3287
3288    @property
3289    def is_star(self) -> bool:
3290        return any(expression.is_star for expression in self.expressions)
3291
3292    @property
3293    def selects(self) -> t.List[Expression]:
3294        return self.expressions
3295
3296
3297class Subquery(DerivedTable, Unionable):
3298    arg_types = {
3299        "this": True,
3300        "alias": False,
3301        "with": False,
3302        **QUERY_MODIFIERS,
3303    }
3304
3305    def unnest(self):
3306        """
3307        Returns the first non subquery.
3308        """
3309        expression = self
3310        while isinstance(expression, Subquery):
3311            expression = expression.this
3312        return expression
3313
3314    def unwrap(self) -> Subquery:
3315        expression = self
3316        while expression.same_parent and expression.is_wrapper:
3317            expression = t.cast(Subquery, expression.parent)
3318        return expression
3319
3320    @property
3321    def is_wrapper(self) -> bool:
3322        """
3323        Whether this Subquery acts as a simple wrapper around another expression.
3324
3325        SELECT * FROM (((SELECT * FROM t)))
3326                      ^
3327                      This corresponds to a "wrapper" Subquery node
3328        """
3329        return all(v is None for k, v in self.args.items() if k != "this")
3330
3331    @property
3332    def is_star(self) -> bool:
3333        return self.this.is_star
3334
3335    @property
3336    def output_name(self) -> str:
3337        return self.alias
3338
3339
3340class TableSample(Expression):
3341    arg_types = {
3342        "this": False,
3343        "method": False,
3344        "bucket_numerator": False,
3345        "bucket_denominator": False,
3346        "bucket_field": False,
3347        "percent": False,
3348        "rows": False,
3349        "size": False,
3350        "seed": False,
3351        "kind": False,
3352    }
3353
3354
3355class Tag(Expression):
3356    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3357
3358    arg_types = {
3359        "this": False,
3360        "prefix": False,
3361        "postfix": False,
3362    }
3363
3364
3365# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3366# https://duckdb.org/docs/sql/statements/pivot
3367class Pivot(Expression):
3368    arg_types = {
3369        "this": False,
3370        "alias": False,
3371        "expressions": True,
3372        "field": False,
3373        "unpivot": False,
3374        "using": False,
3375        "group": False,
3376        "columns": False,
3377        "include_nulls": False,
3378    }
3379
3380
3381class Window(Condition):
3382    arg_types = {
3383        "this": True,
3384        "partition_by": False,
3385        "order": False,
3386        "spec": False,
3387        "alias": False,
3388        "over": False,
3389        "first": False,
3390    }
3391
3392
3393class WindowSpec(Expression):
3394    arg_types = {
3395        "kind": False,
3396        "start": False,
3397        "start_side": False,
3398        "end": False,
3399        "end_side": False,
3400    }
3401
3402
3403class Where(Expression):
3404    pass
3405
3406
3407class Star(Expression):
3408    arg_types = {"except": False, "replace": False}
3409
3410    @property
3411    def name(self) -> str:
3412        return "*"
3413
3414    @property
3415    def output_name(self) -> str:
3416        return self.name
3417
3418
3419class Parameter(Condition):
3420    arg_types = {"this": True, "wrapped": False}
3421
3422
3423class SessionParameter(Condition):
3424    arg_types = {"this": True, "kind": False}
3425
3426
3427class Placeholder(Condition):
3428    arg_types = {"this": False, "kind": False}
3429
3430
3431class Null(Condition):
3432    arg_types: t.Dict[str, t.Any] = {}
3433
3434    @property
3435    def name(self) -> str:
3436        return "NULL"
3437
3438
3439class Boolean(Condition):
3440    pass
3441
3442
3443class DataTypeParam(Expression):
3444    arg_types = {"this": True, "expression": False}
3445
3446
3447class DataType(Expression):
3448    arg_types = {
3449        "this": True,
3450        "expressions": False,
3451        "nested": False,
3452        "values": False,
3453        "prefix": False,
3454        "kind": False,
3455    }
3456
3457    class Type(AutoName):
3458        ARRAY = auto()
3459        BIGDECIMAL = auto()
3460        BIGINT = auto()
3461        BIGSERIAL = auto()
3462        BINARY = auto()
3463        BIT = auto()
3464        BOOLEAN = auto()
3465        CHAR = auto()
3466        DATE = auto()
3467        DATEMULTIRANGE = auto()
3468        DATERANGE = auto()
3469        DATETIME = auto()
3470        DATETIME64 = auto()
3471        DECIMAL = auto()
3472        DOUBLE = auto()
3473        ENUM = auto()
3474        ENUM8 = auto()
3475        ENUM16 = auto()
3476        FIXEDSTRING = auto()
3477        FLOAT = auto()
3478        GEOGRAPHY = auto()
3479        GEOMETRY = auto()
3480        HLLSKETCH = auto()
3481        HSTORE = auto()
3482        IMAGE = auto()
3483        INET = auto()
3484        INT = auto()
3485        INT128 = auto()
3486        INT256 = auto()
3487        INT4MULTIRANGE = auto()
3488        INT4RANGE = auto()
3489        INT8MULTIRANGE = auto()
3490        INT8RANGE = auto()
3491        INTERVAL = auto()
3492        IPADDRESS = auto()
3493        IPPREFIX = auto()
3494        JSON = auto()
3495        JSONB = auto()
3496        LONGBLOB = auto()
3497        LONGTEXT = auto()
3498        LOWCARDINALITY = auto()
3499        MAP = auto()
3500        MEDIUMBLOB = auto()
3501        MEDIUMINT = auto()
3502        MEDIUMTEXT = auto()
3503        MONEY = auto()
3504        NCHAR = auto()
3505        NESTED = auto()
3506        NULL = auto()
3507        NULLABLE = auto()
3508        NUMMULTIRANGE = auto()
3509        NUMRANGE = auto()
3510        NVARCHAR = auto()
3511        OBJECT = auto()
3512        ROWVERSION = auto()
3513        SERIAL = auto()
3514        SET = auto()
3515        SMALLINT = auto()
3516        SMALLMONEY = auto()
3517        SMALLSERIAL = auto()
3518        STRUCT = auto()
3519        SUPER = auto()
3520        TEXT = auto()
3521        TIME = auto()
3522        TIMETZ = auto()
3523        TIMESTAMP = auto()
3524        TIMESTAMPLTZ = auto()
3525        TIMESTAMPTZ = auto()
3526        TINYINT = auto()
3527        TSMULTIRANGE = auto()
3528        TSRANGE = auto()
3529        TSTZMULTIRANGE = auto()
3530        TSTZRANGE = auto()
3531        UBIGINT = auto()
3532        UINT = auto()
3533        UINT128 = auto()
3534        UINT256 = auto()
3535        UNIQUEIDENTIFIER = auto()
3536        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3537        USERDEFINED = "USER-DEFINED"
3538        USMALLINT = auto()
3539        UTINYINT = auto()
3540        UUID = auto()
3541        VARBINARY = auto()
3542        VARCHAR = auto()
3543        VARIANT = auto()
3544        XML = auto()
3545        YEAR = auto()
3546
3547    TEXT_TYPES = {
3548        Type.CHAR,
3549        Type.NCHAR,
3550        Type.VARCHAR,
3551        Type.NVARCHAR,
3552        Type.TEXT,
3553    }
3554
3555    INTEGER_TYPES = {
3556        Type.INT,
3557        Type.TINYINT,
3558        Type.SMALLINT,
3559        Type.BIGINT,
3560        Type.INT128,
3561        Type.INT256,
3562    }
3563
3564    FLOAT_TYPES = {
3565        Type.FLOAT,
3566        Type.DOUBLE,
3567    }
3568
3569    NUMERIC_TYPES = {
3570        *INTEGER_TYPES,
3571        *FLOAT_TYPES,
3572    }
3573
3574    TEMPORAL_TYPES = {
3575        Type.TIME,
3576        Type.TIMETZ,
3577        Type.TIMESTAMP,
3578        Type.TIMESTAMPTZ,
3579        Type.TIMESTAMPLTZ,
3580        Type.DATE,
3581        Type.DATETIME,
3582        Type.DATETIME64,
3583    }
3584
3585    @classmethod
3586    def build(
3587        cls,
3588        dtype: str | DataType | DataType.Type,
3589        dialect: DialectType = None,
3590        udt: bool = False,
3591        **kwargs,
3592    ) -> DataType:
3593        """
3594        Constructs a DataType object.
3595
3596        Args:
3597            dtype: the data type of interest.
3598            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3599            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3600                DataType, thus creating a user-defined type.
3601            kawrgs: additional arguments to pass in the constructor of DataType.
3602
3603        Returns:
3604            The constructed DataType object.
3605        """
3606        from sqlglot import parse_one
3607
3608        if isinstance(dtype, str):
3609            if dtype.upper() == "UNKNOWN":
3610                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3611
3612            try:
3613                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
3614            except ParseError:
3615                if udt:
3616                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3617                raise
3618        elif isinstance(dtype, DataType.Type):
3619            data_type_exp = DataType(this=dtype)
3620        elif isinstance(dtype, DataType):
3621            return dtype
3622        else:
3623            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3624
3625        return DataType(**{**data_type_exp.args, **kwargs})
3626
3627    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3628        """
3629        Checks whether this DataType matches one of the provided data types. Nested types or precision
3630        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3631
3632        Args:
3633            dtypes: the data types to compare this DataType to.
3634
3635        Returns:
3636            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3637        """
3638        for dtype in dtypes:
3639            other = DataType.build(dtype, udt=True)
3640
3641            if (
3642                other.expressions
3643                or self.this == DataType.Type.USERDEFINED
3644                or other.this == DataType.Type.USERDEFINED
3645            ):
3646                matches = self == other
3647            else:
3648                matches = self.this == other.this
3649
3650            if matches:
3651                return True
3652        return False
3653
3654
3655# https://www.postgresql.org/docs/15/datatype-pseudo.html
3656class PseudoType(Expression):
3657    pass
3658
3659
3660# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3661class SubqueryPredicate(Predicate):
3662    pass
3663
3664
3665class All(SubqueryPredicate):
3666    pass
3667
3668
3669class Any(SubqueryPredicate):
3670    pass
3671
3672
3673class Exists(SubqueryPredicate):
3674    pass
3675
3676
3677# Commands to interact with the databases or engines. For most of the command
3678# expressions we parse whatever comes after the command's name as a string.
3679class Command(Expression):
3680    arg_types = {"this": True, "expression": False}
3681
3682
3683class Transaction(Expression):
3684    arg_types = {"this": False, "modes": False, "mark": False}
3685
3686
3687class Commit(Expression):
3688    arg_types = {"chain": False, "this": False, "durability": False}
3689
3690
3691class Rollback(Expression):
3692    arg_types = {"savepoint": False, "this": False}
3693
3694
3695class AlterTable(Expression):
3696    arg_types = {"this": True, "actions": True, "exists": False}
3697
3698
3699class AddConstraint(Expression):
3700    arg_types = {"this": False, "expression": False, "enforced": False}
3701
3702
3703class DropPartition(Expression):
3704    arg_types = {"expressions": True, "exists": False}
3705
3706
3707# Binary expressions like (ADD a b)
3708class Binary(Condition):
3709    arg_types = {"this": True, "expression": True}
3710
3711    @property
3712    def left(self):
3713        return self.this
3714
3715    @property
3716    def right(self):
3717        return self.expression
3718
3719
3720class Add(Binary):
3721    pass
3722
3723
3724class Connector(Binary):
3725    pass
3726
3727
3728class And(Connector):
3729    pass
3730
3731
3732class Or(Connector):
3733    pass
3734
3735
3736class BitwiseAnd(Binary):
3737    pass
3738
3739
3740class BitwiseLeftShift(Binary):
3741    pass
3742
3743
3744class BitwiseOr(Binary):
3745    pass
3746
3747
3748class BitwiseRightShift(Binary):
3749    pass
3750
3751
3752class BitwiseXor(Binary):
3753    pass
3754
3755
3756class Div(Binary):
3757    pass
3758
3759
3760class Overlaps(Binary):
3761    pass
3762
3763
3764class Dot(Binary):
3765    @property
3766    def name(self) -> str:
3767        return self.expression.name
3768
3769    @property
3770    def output_name(self) -> str:
3771        return self.name
3772
3773    @classmethod
3774    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3775        """Build a Dot object with a sequence of expressions."""
3776        if len(expressions) < 2:
3777            raise ValueError(f"Dot requires >= 2 expressions.")
3778
3779        a, b, *expressions = expressions
3780        dot = Dot(this=a, expression=b)
3781
3782        for expression in expressions:
3783            dot = Dot(this=dot, expression=expression)
3784
3785        return dot
3786
3787
3788class DPipe(Binary):
3789    pass
3790
3791
3792class SafeDPipe(DPipe):
3793    pass
3794
3795
3796class EQ(Binary, Predicate):
3797    pass
3798
3799
3800class NullSafeEQ(Binary, Predicate):
3801    pass
3802
3803
3804class NullSafeNEQ(Binary, Predicate):
3805    pass
3806
3807
3808class Distance(Binary):
3809    pass
3810
3811
3812class Escape(Binary):
3813    pass
3814
3815
3816class Glob(Binary, Predicate):
3817    pass
3818
3819
3820class GT(Binary, Predicate):
3821    pass
3822
3823
3824class GTE(Binary, Predicate):
3825    pass
3826
3827
3828class ILike(Binary, Predicate):
3829    pass
3830
3831
3832class ILikeAny(Binary, Predicate):
3833    pass
3834
3835
3836class IntDiv(Binary):
3837    pass
3838
3839
3840class Is(Binary, Predicate):
3841    pass
3842
3843
3844class Kwarg(Binary):
3845    """Kwarg in special functions like func(kwarg => y)."""
3846
3847
3848class Like(Binary, Predicate):
3849    pass
3850
3851
3852class LikeAny(Binary, Predicate):
3853    pass
3854
3855
3856class LT(Binary, Predicate):
3857    pass
3858
3859
3860class LTE(Binary, Predicate):
3861    pass
3862
3863
3864class Mod(Binary):
3865    pass
3866
3867
3868class Mul(Binary):
3869    pass
3870
3871
3872class NEQ(Binary, Predicate):
3873    pass
3874
3875
3876class SimilarTo(Binary, Predicate):
3877    pass
3878
3879
3880class Slice(Binary):
3881    arg_types = {"this": False, "expression": False}
3882
3883
3884class Sub(Binary):
3885    pass
3886
3887
3888class ArrayOverlaps(Binary):
3889    pass
3890
3891
3892# Unary Expressions
3893# (NOT a)
3894class Unary(Condition):
3895    pass
3896
3897
3898class BitwiseNot(Unary):
3899    pass
3900
3901
3902class Not(Unary):
3903    pass
3904
3905
3906class Paren(Unary):
3907    arg_types = {"this": True, "with": False}
3908
3909    @property
3910    def output_name(self) -> str:
3911        return self.this.name
3912
3913
3914class Neg(Unary):
3915    pass
3916
3917
3918class Alias(Expression):
3919    arg_types = {"this": True, "alias": False}
3920
3921    @property
3922    def output_name(self) -> str:
3923        return self.alias
3924
3925
3926class Aliases(Expression):
3927    arg_types = {"this": True, "expressions": True}
3928
3929    @property
3930    def aliases(self):
3931        return self.expressions
3932
3933
3934class AtTimeZone(Expression):
3935    arg_types = {"this": True, "zone": True}
3936
3937
3938class Between(Predicate):
3939    arg_types = {"this": True, "low": True, "high": True}
3940
3941
3942class Bracket(Condition):
3943    arg_types = {"this": True, "expressions": True}
3944
3945
3946class SafeBracket(Bracket):
3947    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
3948
3949
3950class Distinct(Expression):
3951    arg_types = {"expressions": False, "on": False}
3952
3953
3954class In(Predicate):
3955    arg_types = {
3956        "this": True,
3957        "expressions": False,
3958        "query": False,
3959        "unnest": False,
3960        "field": False,
3961        "is_global": False,
3962    }
3963
3964
3965class TimeUnit(Expression):
3966    """Automatically converts unit arg into a var."""
3967
3968    arg_types = {"unit": False}
3969
3970    def __init__(self, **args):
3971        unit = args.get("unit")
3972        if isinstance(unit, (Column, Literal)):
3973            args["unit"] = Var(this=unit.name)
3974        elif isinstance(unit, Week):
3975            unit.set("this", Var(this=unit.this.name))
3976
3977        super().__init__(**args)
3978
3979
3980# https://www.oracletutorial.com/oracle-basics/oracle-interval/
3981# https://trino.io/docs/current/language/types.html#interval-year-to-month
3982class IntervalYearToMonthSpan(Expression):
3983    arg_types = {}
3984
3985
3986# https://www.oracletutorial.com/oracle-basics/oracle-interval/
3987# https://trino.io/docs/current/language/types.html#interval-day-to-second
3988class IntervalDayToSecondSpan(Expression):
3989    arg_types = {}
3990
3991
3992class Interval(TimeUnit):
3993    arg_types = {"this": False, "unit": False}
3994
3995    @property
3996    def unit(self) -> t.Optional[Var]:
3997        return self.args.get("unit")
3998
3999
4000class IgnoreNulls(Expression):
4001    pass
4002
4003
4004class RespectNulls(Expression):
4005    pass
4006
4007
4008# Functions
4009class Func(Condition):
4010    """
4011    The base class for all function expressions.
4012
4013    Attributes:
4014        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4015            treated as a variable length argument and the argument's value will be stored as a list.
4016        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4017            for this function expression. These values are used to map this node to a name during parsing
4018            as well as to provide the function's name during SQL string generation. By default the SQL
4019            name is set to the expression's class name transformed to snake case.
4020    """
4021
4022    is_var_len_args = False
4023
4024    @classmethod
4025    def from_arg_list(cls, args):
4026        if cls.is_var_len_args:
4027            all_arg_keys = list(cls.arg_types)
4028            # If this function supports variable length argument treat the last argument as such.
4029            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4030            num_non_var = len(non_var_len_arg_keys)
4031
4032            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4033            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4034        else:
4035            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4036
4037        return cls(**args_dict)
4038
4039    @classmethod
4040    def sql_names(cls):
4041        if cls is Func:
4042            raise NotImplementedError(
4043                "SQL name is only supported by concrete function implementations"
4044            )
4045        if "_sql_names" not in cls.__dict__:
4046            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4047        return cls._sql_names
4048
4049    @classmethod
4050    def sql_name(cls):
4051        return cls.sql_names()[0]
4052
4053    @classmethod
4054    def default_parser_mappings(cls):
4055        return {name: cls.from_arg_list for name in cls.sql_names()}
4056
4057
4058class AggFunc(Func):
4059    pass
4060
4061
4062class ParameterizedAgg(AggFunc):
4063    arg_types = {"this": True, "expressions": True, "params": True}
4064
4065
4066class Abs(Func):
4067    pass
4068
4069
4070# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4071class Transform(Func):
4072    arg_types = {"this": True, "expression": True}
4073
4074
4075class Anonymous(Func):
4076    arg_types = {"this": True, "expressions": False}
4077    is_var_len_args = True
4078
4079
4080# https://docs.snowflake.com/en/sql-reference/functions/hll
4081# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4082class Hll(AggFunc):
4083    arg_types = {"this": True, "expressions": False}
4084    is_var_len_args = True
4085
4086
4087class ApproxDistinct(AggFunc):
4088    arg_types = {"this": True, "accuracy": False}
4089    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4090
4091
4092class Array(Func):
4093    arg_types = {"expressions": False}
4094    is_var_len_args = True
4095
4096
4097# https://docs.snowflake.com/en/sql-reference/functions/to_char
4098class ToChar(Func):
4099    arg_types = {"this": True, "format": False}
4100
4101
4102class GenerateSeries(Func):
4103    arg_types = {"start": True, "end": True, "step": False}
4104
4105
4106class ArrayAgg(AggFunc):
4107    pass
4108
4109
4110class ArrayAll(Func):
4111    arg_types = {"this": True, "expression": True}
4112
4113
4114class ArrayAny(Func):
4115    arg_types = {"this": True, "expression": True}
4116
4117
4118class ArrayConcat(Func):
4119    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4120    arg_types = {"this": True, "expressions": False}
4121    is_var_len_args = True
4122
4123
4124class ArrayContains(Binary, Func):
4125    pass
4126
4127
4128class ArrayContained(Binary):
4129    pass
4130
4131
4132class ArrayFilter(Func):
4133    arg_types = {"this": True, "expression": True}
4134    _sql_names = ["FILTER", "ARRAY_FILTER"]
4135
4136
4137class ArrayJoin(Func):
4138    arg_types = {"this": True, "expression": True, "null": False}
4139
4140
4141class ArraySize(Func):
4142    arg_types = {"this": True, "expression": False}
4143
4144
4145class ArraySort(Func):
4146    arg_types = {"this": True, "expression": False}
4147
4148
4149class ArraySum(Func):
4150    pass
4151
4152
4153class ArrayUnionAgg(AggFunc):
4154    pass
4155
4156
4157class Avg(AggFunc):
4158    pass
4159
4160
4161class AnyValue(AggFunc):
4162    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4163
4164
4165class First(Func):
4166    arg_types = {"this": True, "ignore_nulls": False}
4167
4168
4169class Last(Func):
4170    arg_types = {"this": True, "ignore_nulls": False}
4171
4172
4173class Case(Func):
4174    arg_types = {"this": False, "ifs": True, "default": False}
4175
4176    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4177        instance = maybe_copy(self, copy)
4178        instance.append(
4179            "ifs",
4180            If(
4181                this=maybe_parse(condition, copy=copy, **opts),
4182                true=maybe_parse(then, copy=copy, **opts),
4183            ),
4184        )
4185        return instance
4186
4187    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4188        instance = maybe_copy(self, copy)
4189        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4190        return instance
4191
4192
4193class Cast(Func):
4194    arg_types = {"this": True, "to": True, "format": False}
4195
4196    @property
4197    def name(self) -> str:
4198        return self.this.name
4199
4200    @property
4201    def to(self) -> DataType:
4202        return self.args["to"]
4203
4204    @property
4205    def output_name(self) -> str:
4206        return self.name
4207
4208    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4209        """
4210        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4211        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4212        array<int> != array<float>.
4213
4214        Args:
4215            dtypes: the data types to compare this Cast's DataType to.
4216
4217        Returns:
4218            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4219        """
4220        return self.to.is_type(*dtypes)
4221
4222
4223class TryCast(Cast):
4224    pass
4225
4226
4227class CastToStrType(Func):
4228    arg_types = {"this": True, "to": True}
4229
4230
4231class Collate(Binary):
4232    pass
4233
4234
4235class Ceil(Func):
4236    arg_types = {"this": True, "decimals": False}
4237    _sql_names = ["CEIL", "CEILING"]
4238
4239
4240class Coalesce(Func):
4241    arg_types = {"this": True, "expressions": False}
4242    is_var_len_args = True
4243    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4244
4245
4246class Concat(Func):
4247    arg_types = {"expressions": True}
4248    is_var_len_args = True
4249
4250
4251class SafeConcat(Concat):
4252    pass
4253
4254
4255class ConcatWs(Concat):
4256    _sql_names = ["CONCAT_WS"]
4257
4258
4259class Count(AggFunc):
4260    arg_types = {"this": False, "expressions": False}
4261    is_var_len_args = True
4262
4263
4264class CountIf(AggFunc):
4265    pass
4266
4267
4268class CurrentDate(Func):
4269    arg_types = {"this": False}
4270
4271
4272class CurrentDatetime(Func):
4273    arg_types = {"this": False}
4274
4275
4276class CurrentTime(Func):
4277    arg_types = {"this": False}
4278
4279
4280class CurrentTimestamp(Func):
4281    arg_types = {"this": False}
4282
4283
4284class CurrentUser(Func):
4285    arg_types = {"this": False}
4286
4287
4288class DateAdd(Func, TimeUnit):
4289    arg_types = {"this": True, "expression": True, "unit": False}
4290
4291
4292class DateSub(Func, TimeUnit):
4293    arg_types = {"this": True, "expression": True, "unit": False}
4294
4295
4296class DateDiff(Func, TimeUnit):
4297    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4298    arg_types = {"this": True, "expression": True, "unit": False}
4299
4300
4301class DateTrunc(Func):
4302    arg_types = {"unit": True, "this": True, "zone": False}
4303
4304
4305class DatetimeAdd(Func, TimeUnit):
4306    arg_types = {"this": True, "expression": True, "unit": False}
4307
4308
4309class DatetimeSub(Func, TimeUnit):
4310    arg_types = {"this": True, "expression": True, "unit": False}
4311
4312
4313class DatetimeDiff(Func, TimeUnit):
4314    arg_types = {"this": True, "expression": True, "unit": False}
4315
4316
4317class DatetimeTrunc(Func, TimeUnit):
4318    arg_types = {"this": True, "unit": True, "zone": False}
4319
4320
4321class DayOfWeek(Func):
4322    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4323
4324
4325class DayOfMonth(Func):
4326    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4327
4328
4329class DayOfYear(Func):
4330    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4331
4332
4333class WeekOfYear(Func):
4334    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4335
4336
4337class MonthsBetween(Func):
4338    arg_types = {"this": True, "expression": True, "roundoff": False}
4339
4340
4341class LastDateOfMonth(Func):
4342    pass
4343
4344
4345class Extract(Func):
4346    arg_types = {"this": True, "expression": True}
4347
4348
4349class TimestampAdd(Func, TimeUnit):
4350    arg_types = {"this": True, "expression": True, "unit": False}
4351
4352
4353class TimestampSub(Func, TimeUnit):
4354    arg_types = {"this": True, "expression": True, "unit": False}
4355
4356
4357class TimestampDiff(Func, TimeUnit):
4358    arg_types = {"this": True, "expression": True, "unit": False}
4359
4360
4361class TimestampTrunc(Func, TimeUnit):
4362    arg_types = {"this": True, "unit": True, "zone": False}
4363
4364
4365class TimeAdd(Func, TimeUnit):
4366    arg_types = {"this": True, "expression": True, "unit": False}
4367
4368
4369class TimeSub(Func, TimeUnit):
4370    arg_types = {"this": True, "expression": True, "unit": False}
4371
4372
4373class TimeDiff(Func, TimeUnit):
4374    arg_types = {"this": True, "expression": True, "unit": False}
4375
4376
4377class TimeTrunc(Func, TimeUnit):
4378    arg_types = {"this": True, "unit": True, "zone": False}
4379
4380
4381class DateFromParts(Func):
4382    _sql_names = ["DATEFROMPARTS"]
4383    arg_types = {"year": True, "month": True, "day": True}
4384
4385
4386class DateStrToDate(Func):
4387    pass
4388
4389
4390class DateToDateStr(Func):
4391    pass
4392
4393
4394class DateToDi(Func):
4395    pass
4396
4397
4398# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4399class Date(Func):
4400    arg_types = {"this": True, "zone": False}
4401
4402
4403class Day(Func):
4404    pass
4405
4406
4407class Decode(Func):
4408    arg_types = {"this": True, "charset": True, "replace": False}
4409
4410
4411class DiToDate(Func):
4412    pass
4413
4414
4415class Encode(Func):
4416    arg_types = {"this": True, "charset": True}
4417
4418
4419class Exp(Func):
4420    pass
4421
4422
4423class Explode(Func):
4424    pass
4425
4426
4427class Floor(Func):
4428    arg_types = {"this": True, "decimals": False}
4429
4430
4431class FromBase64(Func):
4432    pass
4433
4434
4435class ToBase64(Func):
4436    pass
4437
4438
4439class Greatest(Func):
4440    arg_types = {"this": True, "expressions": False}
4441    is_var_len_args = True
4442
4443
4444class GroupConcat(Func):
4445    arg_types = {"this": True, "separator": False}
4446
4447
4448class Hex(Func):
4449    pass
4450
4451
4452class Xor(Connector, Func):
4453    arg_types = {"this": False, "expression": False, "expressions": False}
4454
4455
4456class If(Func):
4457    arg_types = {"this": True, "true": True, "false": False}
4458
4459
4460class Initcap(Func):
4461    arg_types = {"this": True, "expression": False}
4462
4463
4464class IsNan(Func):
4465    _sql_names = ["IS_NAN", "ISNAN"]
4466
4467
4468class JSONKeyValue(Expression):
4469    arg_types = {"this": True, "expression": True}
4470
4471
4472class JSONObject(Func):
4473    arg_types = {
4474        "expressions": False,
4475        "null_handling": False,
4476        "unique_keys": False,
4477        "return_type": False,
4478        "format_json": False,
4479        "encoding": False,
4480    }
4481
4482
4483class OpenJSONColumnDef(Expression):
4484    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4485
4486
4487class OpenJSON(Func):
4488    arg_types = {"this": True, "path": False, "expressions": False}
4489
4490
4491class JSONBContains(Binary):
4492    _sql_names = ["JSONB_CONTAINS"]
4493
4494
4495class JSONExtract(Binary, Func):
4496    _sql_names = ["JSON_EXTRACT"]
4497
4498
4499class JSONExtractScalar(JSONExtract):
4500    _sql_names = ["JSON_EXTRACT_SCALAR"]
4501
4502
4503class JSONBExtract(JSONExtract):
4504    _sql_names = ["JSONB_EXTRACT"]
4505
4506
4507class JSONBExtractScalar(JSONExtract):
4508    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4509
4510
4511class JSONFormat(Func):
4512    arg_types = {"this": False, "options": False}
4513    _sql_names = ["JSON_FORMAT"]
4514
4515
4516# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4517class JSONArrayContains(Binary, Predicate, Func):
4518    _sql_names = ["JSON_ARRAY_CONTAINS"]
4519
4520
4521class Least(Func):
4522    arg_types = {"this": True, "expressions": False}
4523    is_var_len_args = True
4524
4525
4526class Left(Func):
4527    arg_types = {"this": True, "expression": True}
4528
4529
4530class Right(Func):
4531    arg_types = {"this": True, "expression": True}
4532
4533
4534class Length(Func):
4535    _sql_names = ["LENGTH", "LEN"]
4536
4537
4538class Levenshtein(Func):
4539    arg_types = {
4540        "this": True,
4541        "expression": False,
4542        "ins_cost": False,
4543        "del_cost": False,
4544        "sub_cost": False,
4545    }
4546
4547
4548class Ln(Func):
4549    pass
4550
4551
4552class Log(Func):
4553    arg_types = {"this": True, "expression": False}
4554
4555
4556class Log2(Func):
4557    pass
4558
4559
4560class Log10(Func):
4561    pass
4562
4563
4564class LogicalOr(AggFunc):
4565    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4566
4567
4568class LogicalAnd(AggFunc):
4569    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4570
4571
4572class Lower(Func):
4573    _sql_names = ["LOWER", "LCASE"]
4574
4575
4576class Map(Func):
4577    arg_types = {"keys": False, "values": False}
4578
4579
4580class MapFromEntries(Func):
4581    pass
4582
4583
4584class StarMap(Func):
4585    pass
4586
4587
4588class VarMap(Func):
4589    arg_types = {"keys": True, "values": True}
4590    is_var_len_args = True
4591
4592    @property
4593    def keys(self) -> t.List[Expression]:
4594        return self.args["keys"].expressions
4595
4596    @property
4597    def values(self) -> t.List[Expression]:
4598        return self.args["values"].expressions
4599
4600
4601# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4602class MatchAgainst(Func):
4603    arg_types = {"this": True, "expressions": True, "modifier": False}
4604
4605
4606class Max(AggFunc):
4607    arg_types = {"this": True, "expressions": False}
4608    is_var_len_args = True
4609
4610
4611class MD5(Func):
4612    _sql_names = ["MD5"]
4613
4614
4615# Represents the variant of the MD5 function that returns a binary value
4616class MD5Digest(Func):
4617    _sql_names = ["MD5_DIGEST"]
4618
4619
4620class Min(AggFunc):
4621    arg_types = {"this": True, "expressions": False}
4622    is_var_len_args = True
4623
4624
4625class Month(Func):
4626    pass
4627
4628
4629class Nvl2(Func):
4630    arg_types = {"this": True, "true": True, "false": False}
4631
4632
4633class Posexplode(Func):
4634    pass
4635
4636
4637class Pow(Binary, Func):
4638    _sql_names = ["POWER", "POW"]
4639
4640
4641class PercentileCont(AggFunc):
4642    arg_types = {"this": True, "expression": False}
4643
4644
4645class PercentileDisc(AggFunc):
4646    arg_types = {"this": True, "expression": False}
4647
4648
4649class Quantile(AggFunc):
4650    arg_types = {"this": True, "quantile": True}
4651
4652
4653class ApproxQuantile(Quantile):
4654    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4655
4656
4657class RangeN(Func):
4658    arg_types = {"this": True, "expressions": True, "each": False}
4659
4660
4661class ReadCSV(Func):
4662    _sql_names = ["READ_CSV"]
4663    is_var_len_args = True
4664    arg_types = {"this": True, "expressions": False}
4665
4666
4667class Reduce(Func):
4668    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4669
4670
4671class RegexpExtract(Func):
4672    arg_types = {
4673        "this": True,
4674        "expression": True,
4675        "position": False,
4676        "occurrence": False,
4677        "parameters": False,
4678        "group": False,
4679    }
4680
4681
4682class RegexpReplace(Func):
4683    arg_types = {
4684        "this": True,
4685        "expression": True,
4686        "replacement": True,
4687        "position": False,
4688        "occurrence": False,
4689        "parameters": False,
4690    }
4691
4692
4693class RegexpLike(Binary, Func):
4694    arg_types = {"this": True, "expression": True, "flag": False}
4695
4696
4697class RegexpILike(Func):
4698    arg_types = {"this": True, "expression": True, "flag": False}
4699
4700
4701# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4702# limit is the number of times a pattern is applied
4703class RegexpSplit(Func):
4704    arg_types = {"this": True, "expression": True, "limit": False}
4705
4706
4707class Repeat(Func):
4708    arg_types = {"this": True, "times": True}
4709
4710
4711class Round(Func):
4712    arg_types = {"this": True, "decimals": False}
4713
4714
4715class RowNumber(Func):
4716    arg_types: t.Dict[str, t.Any] = {}
4717
4718
4719class SafeDivide(Func):
4720    arg_types = {"this": True, "expression": True}
4721
4722
4723class SetAgg(AggFunc):
4724    pass
4725
4726
4727class SHA(Func):
4728    _sql_names = ["SHA", "SHA1"]
4729
4730
4731class SHA2(Func):
4732    _sql_names = ["SHA2"]
4733    arg_types = {"this": True, "length": False}
4734
4735
4736class SortArray(Func):
4737    arg_types = {"this": True, "asc": False}
4738
4739
4740class Split(Func):
4741    arg_types = {"this": True, "expression": True, "limit": False}
4742
4743
4744# Start may be omitted in the case of postgres
4745# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4746class Substring(Func):
4747    arg_types = {"this": True, "start": False, "length": False}
4748
4749
4750class StandardHash(Func):
4751    arg_types = {"this": True, "expression": False}
4752
4753
4754class StartsWith(Func):
4755    _sql_names = ["STARTS_WITH", "STARTSWITH"]
4756    arg_types = {"this": True, "expression": True}
4757
4758
4759class StrPosition(Func):
4760    arg_types = {
4761        "this": True,
4762        "substr": True,
4763        "position": False,
4764        "instance": False,
4765    }
4766
4767
4768class StrToDate(Func):
4769    arg_types = {"this": True, "format": True}
4770
4771
4772class StrToTime(Func):
4773    arg_types = {"this": True, "format": True, "zone": False}
4774
4775
4776# Spark allows unix_timestamp()
4777# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
4778class StrToUnix(Func):
4779    arg_types = {"this": False, "format": False}
4780
4781
4782# https://prestodb.io/docs/current/functions/string.html
4783# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
4784class StrToMap(Func):
4785    arg_types = {
4786        "this": True,
4787        "pair_delim": False,
4788        "key_value_delim": False,
4789        "duplicate_resolution_callback": False,
4790    }
4791
4792
4793class NumberToStr(Func):
4794    arg_types = {"this": True, "format": True, "culture": False}
4795
4796
4797class FromBase(Func):
4798    arg_types = {"this": True, "expression": True}
4799
4800
4801class Struct(Func):
4802    arg_types = {"expressions": True}
4803    is_var_len_args = True
4804
4805
4806class StructExtract(Func):
4807    arg_types = {"this": True, "expression": True}
4808
4809
4810# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
4811# https://docs.snowflake.com/en/sql-reference/functions/insert
4812class Stuff(Func):
4813    _sql_names = ["STUFF", "INSERT"]
4814    arg_types = {"this": True, "start": True, "length": True, "expression": True}
4815
4816
4817class Sum(AggFunc):
4818    pass
4819
4820
4821class Sqrt(Func):
4822    pass
4823
4824
4825class Stddev(AggFunc):
4826    pass
4827
4828
4829class StddevPop(AggFunc):
4830    pass
4831
4832
4833class StddevSamp(AggFunc):
4834    pass
4835
4836
4837class TimeToStr(Func):
4838    arg_types = {"this": True, "format": True, "culture": False}
4839
4840
4841class TimeToTimeStr(Func):
4842    pass
4843
4844
4845class TimeToUnix(Func):
4846    pass
4847
4848
4849class TimeStrToDate(Func):
4850    pass
4851
4852
4853class TimeStrToTime(Func):
4854    pass
4855
4856
4857class TimeStrToUnix(Func):
4858    pass
4859
4860
4861class Trim(Func):
4862    arg_types = {
4863        "this": True,
4864        "expression": False,
4865        "position": False,
4866        "collation": False,
4867    }
4868
4869
4870class TsOrDsAdd(Func, TimeUnit):
4871    arg_types = {"this": True, "expression": True, "unit": False}
4872
4873
4874class TsOrDsToDateStr(Func):
4875    pass
4876
4877
4878class TsOrDsToDate(Func):
4879    arg_types = {"this": True, "format": False}
4880
4881
4882class TsOrDiToDi(Func):
4883    pass
4884
4885
4886class Unhex(Func):
4887    pass
4888
4889
4890class UnixToStr(Func):
4891    arg_types = {"this": True, "format": False}
4892
4893
4894# https://prestodb.io/docs/current/functions/datetime.html
4895# presto has weird zone/hours/minutes
4896class UnixToTime(Func):
4897    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
4898
4899    SECONDS = Literal.string("seconds")
4900    MILLIS = Literal.string("millis")
4901    MICROS = Literal.string("micros")
4902
4903
4904class UnixToTimeStr(Func):
4905    pass
4906
4907
4908class Upper(Func):
4909    _sql_names = ["UPPER", "UCASE"]
4910
4911
4912class Variance(AggFunc):
4913    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4914
4915
4916class VariancePop(AggFunc):
4917    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4918
4919
4920class Week(Func):
4921    arg_types = {"this": True, "mode": False}
4922
4923
4924class XMLTable(Func):
4925    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
4926
4927
4928class Year(Func):
4929    pass
4930
4931
4932class Use(Expression):
4933    arg_types = {"this": True, "kind": False}
4934
4935
4936class Merge(Expression):
4937    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
4938
4939
4940class When(Func):
4941    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
4942
4943
4944# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
4945# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
4946class NextValueFor(Func):
4947    arg_types = {"this": True, "order": False}
4948
4949
4950def _norm_arg(arg):
4951    return arg.lower() if type(arg) is str else arg
4952
4953
4954ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
4955
4956
4957# Helpers
4958@t.overload
4959def maybe_parse(
4960    sql_or_expression: ExpOrStr,
4961    *,
4962    into: t.Type[E],
4963    dialect: DialectType = None,
4964    prefix: t.Optional[str] = None,
4965    copy: bool = False,
4966    **opts,
4967) -> E:
4968    ...
4969
4970
4971@t.overload
4972def maybe_parse(
4973    sql_or_expression: str | E,
4974    *,
4975    into: t.Optional[IntoType] = None,
4976    dialect: DialectType = None,
4977    prefix: t.Optional[str] = None,
4978    copy: bool = False,
4979    **opts,
4980) -> E:
4981    ...
4982
4983
4984def maybe_parse(
4985    sql_or_expression: ExpOrStr,
4986    *,
4987    into: t.Optional[IntoType] = None,
4988    dialect: DialectType = None,
4989    prefix: t.Optional[str] = None,
4990    copy: bool = False,
4991    **opts,
4992) -> Expression:
4993    """Gracefully handle a possible string or expression.
4994
4995    Example:
4996        >>> maybe_parse("1")
4997        (LITERAL this: 1, is_string: False)
4998        >>> maybe_parse(to_identifier("x"))
4999        (IDENTIFIER this: x, quoted: False)
5000
5001    Args:
5002        sql_or_expression: the SQL code string or an expression
5003        into: the SQLGlot Expression to parse into
5004        dialect: the dialect used to parse the input expressions (in the case that an
5005            input expression is a SQL string).
5006        prefix: a string to prefix the sql with before it gets parsed
5007            (automatically includes a space)
5008        copy: whether or not to copy the expression.
5009        **opts: other options to use to parse the input expressions (again, in the case
5010            that an input expression is a SQL string).
5011
5012    Returns:
5013        Expression: the parsed or given expression.
5014    """
5015    if isinstance(sql_or_expression, Expression):
5016        if copy:
5017            return sql_or_expression.copy()
5018        return sql_or_expression
5019
5020    if sql_or_expression is None:
5021        raise ParseError(f"SQL cannot be None")
5022
5023    import sqlglot
5024
5025    sql = str(sql_or_expression)
5026    if prefix:
5027        sql = f"{prefix} {sql}"
5028
5029    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5030
5031
5032@t.overload
5033def maybe_copy(instance: None, copy: bool = True) -> None:
5034    ...
5035
5036
5037@t.overload
5038def maybe_copy(instance: E, copy: bool = True) -> E:
5039    ...
5040
5041
5042def maybe_copy(instance, copy=True):
5043    return instance.copy() if copy and instance else instance
5044
5045
5046def _is_wrong_expression(expression, into):
5047    return isinstance(expression, Expression) and not isinstance(expression, into)
5048
5049
5050def _apply_builder(
5051    expression,
5052    instance,
5053    arg,
5054    copy=True,
5055    prefix=None,
5056    into=None,
5057    dialect=None,
5058    **opts,
5059):
5060    if _is_wrong_expression(expression, into):
5061        expression = into(this=expression)
5062    instance = maybe_copy(instance, copy)
5063    expression = maybe_parse(
5064        sql_or_expression=expression,
5065        prefix=prefix,
5066        into=into,
5067        dialect=dialect,
5068        **opts,
5069    )
5070    instance.set(arg, expression)
5071    return instance
5072
5073
5074def _apply_child_list_builder(
5075    *expressions,
5076    instance,
5077    arg,
5078    append=True,
5079    copy=True,
5080    prefix=None,
5081    into=None,
5082    dialect=None,
5083    properties=None,
5084    **opts,
5085):
5086    instance = maybe_copy(instance, copy)
5087    parsed = []
5088    for expression in expressions:
5089        if expression is not None:
5090            if _is_wrong_expression(expression, into):
5091                expression = into(expressions=[expression])
5092
5093            expression = maybe_parse(
5094                expression,
5095                into=into,
5096                dialect=dialect,
5097                prefix=prefix,
5098                **opts,
5099            )
5100            parsed.extend(expression.expressions)
5101
5102    existing = instance.args.get(arg)
5103    if append and existing:
5104        parsed = existing.expressions + parsed
5105
5106    child = into(expressions=parsed)
5107    for k, v in (properties or {}).items():
5108        child.set(k, v)
5109    instance.set(arg, child)
5110
5111    return instance
5112
5113
5114def _apply_list_builder(
5115    *expressions,
5116    instance,
5117    arg,
5118    append=True,
5119    copy=True,
5120    prefix=None,
5121    into=None,
5122    dialect=None,
5123    **opts,
5124):
5125    inst = maybe_copy(instance, copy)
5126
5127    expressions = [
5128        maybe_parse(
5129            sql_or_expression=expression,
5130            into=into,
5131            prefix=prefix,
5132            dialect=dialect,
5133            **opts,
5134        )
5135        for expression in expressions
5136        if expression is not None
5137    ]
5138
5139    existing_expressions = inst.args.get(arg)
5140    if append and existing_expressions:
5141        expressions = existing_expressions + expressions
5142
5143    inst.set(arg, expressions)
5144    return inst
5145
5146
5147def _apply_conjunction_builder(
5148    *expressions,
5149    instance,
5150    arg,
5151    into=None,
5152    append=True,
5153    copy=True,
5154    dialect=None,
5155    **opts,
5156):
5157    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5158    if not expressions:
5159        return instance
5160
5161    inst = maybe_copy(instance, copy)
5162
5163    existing = inst.args.get(arg)
5164    if append and existing is not None:
5165        expressions = [existing.this if into else existing] + list(expressions)
5166
5167    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5168
5169    inst.set(arg, into(this=node) if into else node)
5170    return inst
5171
5172
5173def _apply_cte_builder(
5174    instance: E,
5175    alias: ExpOrStr,
5176    as_: ExpOrStr,
5177    recursive: t.Optional[bool] = None,
5178    append: bool = True,
5179    dialect: DialectType = None,
5180    copy: bool = True,
5181    **opts,
5182) -> E:
5183    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5184    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5185    cte = CTE(this=as_expression, alias=alias_expression)
5186    return _apply_child_list_builder(
5187        cte,
5188        instance=instance,
5189        arg="with",
5190        append=append,
5191        copy=copy,
5192        into=With,
5193        properties={"recursive": recursive or False},
5194    )
5195
5196
5197def _combine(
5198    expressions: t.Sequence[t.Optional[ExpOrStr]],
5199    operator: t.Type[Connector],
5200    dialect: DialectType = None,
5201    copy: bool = True,
5202    **opts,
5203) -> Expression:
5204    conditions = [
5205        condition(expression, dialect=dialect, copy=copy, **opts)
5206        for expression in expressions
5207        if expression is not None
5208    ]
5209
5210    this, *rest = conditions
5211    if rest:
5212        this = _wrap(this, Connector)
5213    for expression in rest:
5214        this = operator(this=this, expression=_wrap(expression, Connector))
5215
5216    return this
5217
5218
5219def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5220    return Paren(this=expression) if isinstance(expression, kind) else expression
5221
5222
5223def union(
5224    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5225) -> Union:
5226    """
5227    Initializes a syntax tree from one UNION expression.
5228
5229    Example:
5230        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5231        'SELECT * FROM foo UNION SELECT * FROM bla'
5232
5233    Args:
5234        left: the SQL code string corresponding to the left-hand side.
5235            If an `Expression` instance is passed, it will be used as-is.
5236        right: the SQL code string corresponding to the right-hand side.
5237            If an `Expression` instance is passed, it will be used as-is.
5238        distinct: set the DISTINCT flag if and only if this is true.
5239        dialect: the dialect used to parse the input expression.
5240        opts: other options to use to parse the input expressions.
5241
5242    Returns:
5243        The new Union instance.
5244    """
5245    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5246    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5247
5248    return Union(this=left, expression=right, distinct=distinct)
5249
5250
5251def intersect(
5252    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5253) -> Intersect:
5254    """
5255    Initializes a syntax tree from one INTERSECT expression.
5256
5257    Example:
5258        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5259        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5260
5261    Args:
5262        left: the SQL code string corresponding to the left-hand side.
5263            If an `Expression` instance is passed, it will be used as-is.
5264        right: the SQL code string corresponding to the right-hand side.
5265            If an `Expression` instance is passed, it will be used as-is.
5266        distinct: set the DISTINCT flag if and only if this is true.
5267        dialect: the dialect used to parse the input expression.
5268        opts: other options to use to parse the input expressions.
5269
5270    Returns:
5271        The new Intersect instance.
5272    """
5273    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5274    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5275
5276    return Intersect(this=left, expression=right, distinct=distinct)
5277
5278
5279def except_(
5280    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5281) -> Except:
5282    """
5283    Initializes a syntax tree from one EXCEPT expression.
5284
5285    Example:
5286        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5287        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5288
5289    Args:
5290        left: the SQL code string corresponding to the left-hand side.
5291            If an `Expression` instance is passed, it will be used as-is.
5292        right: the SQL code string corresponding to the right-hand side.
5293            If an `Expression` instance is passed, it will be used as-is.
5294        distinct: set the DISTINCT flag if and only if this is true.
5295        dialect: the dialect used to parse the input expression.
5296        opts: other options to use to parse the input expressions.
5297
5298    Returns:
5299        The new Except instance.
5300    """
5301    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5302    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5303
5304    return Except(this=left, expression=right, distinct=distinct)
5305
5306
5307def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5308    """
5309    Initializes a syntax tree from one or multiple SELECT expressions.
5310
5311    Example:
5312        >>> select("col1", "col2").from_("tbl").sql()
5313        'SELECT col1, col2 FROM tbl'
5314
5315    Args:
5316        *expressions: the SQL code string to parse as the expressions of a
5317            SELECT statement. If an Expression instance is passed, this is used as-is.
5318        dialect: the dialect used to parse the input expressions (in the case that an
5319            input expression is a SQL string).
5320        **opts: other options to use to parse the input expressions (again, in the case
5321            that an input expression is a SQL string).
5322
5323    Returns:
5324        Select: the syntax tree for the SELECT statement.
5325    """
5326    return Select().select(*expressions, dialect=dialect, **opts)
5327
5328
5329def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5330    """
5331    Initializes a syntax tree from a FROM expression.
5332
5333    Example:
5334        >>> from_("tbl").select("col1", "col2").sql()
5335        'SELECT col1, col2 FROM tbl'
5336
5337    Args:
5338        *expression: the SQL code string to parse as the FROM expressions of a
5339            SELECT statement. If an Expression instance is passed, this is used as-is.
5340        dialect: the dialect used to parse the input expression (in the case that the
5341            input expression is a SQL string).
5342        **opts: other options to use to parse the input expressions (again, in the case
5343            that the input expression is a SQL string).
5344
5345    Returns:
5346        Select: the syntax tree for the SELECT statement.
5347    """
5348    return Select().from_(expression, dialect=dialect, **opts)
5349
5350
5351def update(
5352    table: str | Table,
5353    properties: dict,
5354    where: t.Optional[ExpOrStr] = None,
5355    from_: t.Optional[ExpOrStr] = None,
5356    dialect: DialectType = None,
5357    **opts,
5358) -> Update:
5359    """
5360    Creates an update statement.
5361
5362    Example:
5363        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5364        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5365
5366    Args:
5367        *properties: dictionary of properties to set which are
5368            auto converted to sql objects eg None -> NULL
5369        where: sql conditional parsed into a WHERE statement
5370        from_: sql statement parsed into a FROM statement
5371        dialect: the dialect used to parse the input expressions.
5372        **opts: other options to use to parse the input expressions.
5373
5374    Returns:
5375        Update: the syntax tree for the UPDATE statement.
5376    """
5377    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5378    update_expr.set(
5379        "expressions",
5380        [
5381            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5382            for k, v in properties.items()
5383        ],
5384    )
5385    if from_:
5386        update_expr.set(
5387            "from",
5388            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5389        )
5390    if isinstance(where, Condition):
5391        where = Where(this=where)
5392    if where:
5393        update_expr.set(
5394            "where",
5395            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5396        )
5397    return update_expr
5398
5399
5400def delete(
5401    table: ExpOrStr,
5402    where: t.Optional[ExpOrStr] = None,
5403    returning: t.Optional[ExpOrStr] = None,
5404    dialect: DialectType = None,
5405    **opts,
5406) -> Delete:
5407    """
5408    Builds a delete statement.
5409
5410    Example:
5411        >>> delete("my_table", where="id > 1").sql()
5412        'DELETE FROM my_table WHERE id > 1'
5413
5414    Args:
5415        where: sql conditional parsed into a WHERE statement
5416        returning: sql conditional parsed into a RETURNING statement
5417        dialect: the dialect used to parse the input expressions.
5418        **opts: other options to use to parse the input expressions.
5419
5420    Returns:
5421        Delete: the syntax tree for the DELETE statement.
5422    """
5423    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5424    if where:
5425        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5426    if returning:
5427        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5428    return delete_expr
5429
5430
5431def insert(
5432    expression: ExpOrStr,
5433    into: ExpOrStr,
5434    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5435    overwrite: t.Optional[bool] = None,
5436    dialect: DialectType = None,
5437    copy: bool = True,
5438    **opts,
5439) -> Insert:
5440    """
5441    Builds an INSERT statement.
5442
5443    Example:
5444        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5445        'INSERT INTO tbl VALUES (1, 2, 3)'
5446
5447    Args:
5448        expression: the sql string or expression of the INSERT statement
5449        into: the tbl to insert data to.
5450        columns: optionally the table's column names.
5451        overwrite: whether to INSERT OVERWRITE or not.
5452        dialect: the dialect used to parse the input expressions.
5453        copy: whether or not to copy the expression.
5454        **opts: other options to use to parse the input expressions.
5455
5456    Returns:
5457        Insert: the syntax tree for the INSERT statement.
5458    """
5459    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5460    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5461
5462    if columns:
5463        this = _apply_list_builder(
5464            *columns,
5465            instance=Schema(this=this),
5466            arg="expressions",
5467            into=Identifier,
5468            copy=False,
5469            dialect=dialect,
5470            **opts,
5471        )
5472
5473    return Insert(this=this, expression=expr, overwrite=overwrite)
5474
5475
5476def condition(
5477    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5478) -> Condition:
5479    """
5480    Initialize a logical condition expression.
5481
5482    Example:
5483        >>> condition("x=1").sql()
5484        'x = 1'
5485
5486        This is helpful for composing larger logical syntax trees:
5487        >>> where = condition("x=1")
5488        >>> where = where.and_("y=1")
5489        >>> Select().from_("tbl").select("*").where(where).sql()
5490        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5491
5492    Args:
5493        *expression: the SQL code string to parse.
5494            If an Expression instance is passed, this is used as-is.
5495        dialect: the dialect used to parse the input expression (in the case that the
5496            input expression is a SQL string).
5497        copy: Whether or not to copy `expression` (only applies to expressions).
5498        **opts: other options to use to parse the input expressions (again, in the case
5499            that the input expression is a SQL string).
5500
5501    Returns:
5502        The new Condition instance
5503    """
5504    return maybe_parse(
5505        expression,
5506        into=Condition,
5507        dialect=dialect,
5508        copy=copy,
5509        **opts,
5510    )
5511
5512
5513def and_(
5514    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5515) -> Condition:
5516    """
5517    Combine multiple conditions with an AND logical operator.
5518
5519    Example:
5520        >>> and_("x=1", and_("y=1", "z=1")).sql()
5521        'x = 1 AND (y = 1 AND z = 1)'
5522
5523    Args:
5524        *expressions: the SQL code strings to parse.
5525            If an Expression instance is passed, this is used as-is.
5526        dialect: the dialect used to parse the input expression.
5527        copy: whether or not to copy `expressions` (only applies to Expressions).
5528        **opts: other options to use to parse the input expressions.
5529
5530    Returns:
5531        And: the new condition
5532    """
5533    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5534
5535
5536def or_(
5537    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5538) -> Condition:
5539    """
5540    Combine multiple conditions with an OR logical operator.
5541
5542    Example:
5543        >>> or_("x=1", or_("y=1", "z=1")).sql()
5544        'x = 1 OR (y = 1 OR z = 1)'
5545
5546    Args:
5547        *expressions: the SQL code strings to parse.
5548            If an Expression instance is passed, this is used as-is.
5549        dialect: the dialect used to parse the input expression.
5550        copy: whether or not to copy `expressions` (only applies to Expressions).
5551        **opts: other options to use to parse the input expressions.
5552
5553    Returns:
5554        Or: the new condition
5555    """
5556    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5557
5558
5559def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5560    """
5561    Wrap a condition with a NOT operator.
5562
5563    Example:
5564        >>> not_("this_suit='black'").sql()
5565        "NOT this_suit = 'black'"
5566
5567    Args:
5568        expression: the SQL code string to parse.
5569            If an Expression instance is passed, this is used as-is.
5570        dialect: the dialect used to parse the input expression.
5571        copy: whether to copy the expression or not.
5572        **opts: other options to use to parse the input expressions.
5573
5574    Returns:
5575        The new condition.
5576    """
5577    this = condition(
5578        expression,
5579        dialect=dialect,
5580        copy=copy,
5581        **opts,
5582    )
5583    return Not(this=_wrap(this, Connector))
5584
5585
5586def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5587    """
5588    Wrap an expression in parentheses.
5589
5590    Example:
5591        >>> paren("5 + 3").sql()
5592        '(5 + 3)'
5593
5594    Args:
5595        expression: the SQL code string to parse.
5596            If an Expression instance is passed, this is used as-is.
5597        copy: whether to copy the expression or not.
5598
5599    Returns:
5600        The wrapped expression.
5601    """
5602    return Paren(this=maybe_parse(expression, copy=copy))
5603
5604
5605SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5606
5607
5608@t.overload
5609def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5610    ...
5611
5612
5613@t.overload
5614def to_identifier(
5615    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5616) -> Identifier:
5617    ...
5618
5619
5620def to_identifier(name, quoted=None, copy=True):
5621    """Builds an identifier.
5622
5623    Args:
5624        name: The name to turn into an identifier.
5625        quoted: Whether or not force quote the identifier.
5626        copy: Whether or not to copy a passed in Identefier node.
5627
5628    Returns:
5629        The identifier ast node.
5630    """
5631
5632    if name is None:
5633        return None
5634
5635    if isinstance(name, Identifier):
5636        identifier = maybe_copy(name, copy)
5637    elif isinstance(name, str):
5638        identifier = Identifier(
5639            this=name,
5640            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5641        )
5642    else:
5643        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5644    return identifier
5645
5646
5647INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5648
5649
5650def to_interval(interval: str | Literal) -> Interval:
5651    """Builds an interval expression from a string like '1 day' or '5 months'."""
5652    if isinstance(interval, Literal):
5653        if not interval.is_string:
5654            raise ValueError("Invalid interval string.")
5655
5656        interval = interval.this
5657
5658    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5659
5660    if not interval_parts:
5661        raise ValueError("Invalid interval string.")
5662
5663    return Interval(
5664        this=Literal.string(interval_parts.group(1)),
5665        unit=Var(this=interval_parts.group(2)),
5666    )
5667
5668
5669@t.overload
5670def to_table(sql_path: str | Table, **kwargs) -> Table:
5671    ...
5672
5673
5674@t.overload
5675def to_table(sql_path: None, **kwargs) -> None:
5676    ...
5677
5678
5679def to_table(
5680    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5681) -> t.Optional[Table]:
5682    """
5683    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5684    If a table is passed in then that table is returned.
5685
5686    Args:
5687        sql_path: a `[catalog].[schema].[table]` string.
5688        dialect: the source dialect according to which the table name will be parsed.
5689        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5690
5691    Returns:
5692        A table expression.
5693    """
5694    if sql_path is None or isinstance(sql_path, Table):
5695        return sql_path
5696    if not isinstance(sql_path, str):
5697        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5698
5699    table = maybe_parse(sql_path, into=Table, dialect=dialect)
5700    if table:
5701        for k, v in kwargs.items():
5702            table.set(k, v)
5703
5704    return table
5705
5706
5707def to_column(sql_path: str | Column, **kwargs) -> Column:
5708    """
5709    Create a column from a `[table].[column]` sql path. Schema is optional.
5710
5711    If a column is passed in then that column is returned.
5712
5713    Args:
5714        sql_path: `[table].[column]` string
5715    Returns:
5716        Table: A column expression
5717    """
5718    if sql_path is None or isinstance(sql_path, Column):
5719        return sql_path
5720    if not isinstance(sql_path, str):
5721        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
5722    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
5723
5724
5725def alias_(
5726    expression: ExpOrStr,
5727    alias: str | Identifier,
5728    table: bool | t.Sequence[str | Identifier] = False,
5729    quoted: t.Optional[bool] = None,
5730    dialect: DialectType = None,
5731    copy: bool = True,
5732    **opts,
5733):
5734    """Create an Alias expression.
5735
5736    Example:
5737        >>> alias_('foo', 'bar').sql()
5738        'foo AS bar'
5739
5740        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
5741        '(SELECT 1, 2) AS bar(a, b)'
5742
5743    Args:
5744        expression: the SQL code strings to parse.
5745            If an Expression instance is passed, this is used as-is.
5746        alias: the alias name to use. If the name has
5747            special characters it is quoted.
5748        table: Whether or not to create a table alias, can also be a list of columns.
5749        quoted: whether or not to quote the alias
5750        dialect: the dialect used to parse the input expression.
5751        copy: Whether or not to copy the expression.
5752        **opts: other options to use to parse the input expressions.
5753
5754    Returns:
5755        Alias: the aliased expression
5756    """
5757    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5758    alias = to_identifier(alias, quoted=quoted)
5759
5760    if table:
5761        table_alias = TableAlias(this=alias)
5762        exp.set("alias", table_alias)
5763
5764        if not isinstance(table, bool):
5765            for column in table:
5766                table_alias.append("columns", to_identifier(column, quoted=quoted))
5767
5768        return exp
5769
5770    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
5771    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
5772    # for the complete Window expression.
5773    #
5774    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
5775
5776    if "alias" in exp.arg_types and not isinstance(exp, Window):
5777        exp.set("alias", alias)
5778        return exp
5779    return Alias(this=exp, alias=alias)
5780
5781
5782def subquery(
5783    expression: ExpOrStr,
5784    alias: t.Optional[Identifier | str] = None,
5785    dialect: DialectType = None,
5786    **opts,
5787) -> Select:
5788    """
5789    Build a subquery expression.
5790
5791    Example:
5792        >>> subquery('select x from tbl', 'bar').select('x').sql()
5793        'SELECT x FROM (SELECT x FROM tbl) AS bar'
5794
5795    Args:
5796        expression: the SQL code strings to parse.
5797            If an Expression instance is passed, this is used as-is.
5798        alias: the alias name to use.
5799        dialect: the dialect used to parse the input expression.
5800        **opts: other options to use to parse the input expressions.
5801
5802    Returns:
5803        A new Select instance with the subquery expression included.
5804    """
5805
5806    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
5807    return Select().from_(expression, dialect=dialect, **opts)
5808
5809
5810def column(
5811    col: str | Identifier,
5812    table: t.Optional[str | Identifier] = None,
5813    db: t.Optional[str | Identifier] = None,
5814    catalog: t.Optional[str | Identifier] = None,
5815    quoted: t.Optional[bool] = None,
5816) -> Column:
5817    """
5818    Build a Column.
5819
5820    Args:
5821        col: Column name.
5822        table: Table name.
5823        db: Database name.
5824        catalog: Catalog name.
5825        quoted: Whether to force quotes on the column's identifiers.
5826
5827    Returns:
5828        The new Column instance.
5829    """
5830    return Column(
5831        this=to_identifier(col, quoted=quoted),
5832        table=to_identifier(table, quoted=quoted),
5833        db=to_identifier(db, quoted=quoted),
5834        catalog=to_identifier(catalog, quoted=quoted),
5835    )
5836
5837
5838def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
5839    """Cast an expression to a data type.
5840
5841    Example:
5842        >>> cast('x + 1', 'int').sql()
5843        'CAST(x + 1 AS INT)'
5844
5845    Args:
5846        expression: The expression to cast.
5847        to: The datatype to cast to.
5848
5849    Returns:
5850        The new Cast instance.
5851    """
5852    expression = maybe_parse(expression, **opts)
5853    return Cast(this=expression, to=DataType.build(to, **opts))
5854
5855
5856def table_(
5857    table: Identifier | str,
5858    db: t.Optional[Identifier | str] = None,
5859    catalog: t.Optional[Identifier | str] = None,
5860    quoted: t.Optional[bool] = None,
5861    alias: t.Optional[Identifier | str] = None,
5862) -> Table:
5863    """Build a Table.
5864
5865    Args:
5866        table: Table name.
5867        db: Database name.
5868        catalog: Catalog name.
5869        quote: Whether to force quotes on the table's identifiers.
5870        alias: Table's alias.
5871
5872    Returns:
5873        The new Table instance.
5874    """
5875    return Table(
5876        this=to_identifier(table, quoted=quoted) if table else None,
5877        db=to_identifier(db, quoted=quoted) if db else None,
5878        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
5879        alias=TableAlias(this=to_identifier(alias)) if alias else None,
5880    )
5881
5882
5883def values(
5884    values: t.Iterable[t.Tuple[t.Any, ...]],
5885    alias: t.Optional[str] = None,
5886    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
5887) -> Values:
5888    """Build VALUES statement.
5889
5890    Example:
5891        >>> values([(1, '2')]).sql()
5892        "VALUES (1, '2')"
5893
5894    Args:
5895        values: values statements that will be converted to SQL
5896        alias: optional alias
5897        columns: Optional list of ordered column names or ordered dictionary of column names to types.
5898         If either are provided then an alias is also required.
5899
5900    Returns:
5901        Values: the Values expression object
5902    """
5903    if columns and not alias:
5904        raise ValueError("Alias is required when providing columns")
5905
5906    return Values(
5907        expressions=[convert(tup) for tup in values],
5908        alias=(
5909            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
5910            if columns
5911            else (TableAlias(this=to_identifier(alias)) if alias else None)
5912        ),
5913    )
5914
5915
5916def var(name: t.Optional[ExpOrStr]) -> Var:
5917    """Build a SQL variable.
5918
5919    Example:
5920        >>> repr(var('x'))
5921        '(VAR this: x)'
5922
5923        >>> repr(var(column('x', table='y')))
5924        '(VAR this: x)'
5925
5926    Args:
5927        name: The name of the var or an expression who's name will become the var.
5928
5929    Returns:
5930        The new variable node.
5931    """
5932    if not name:
5933        raise ValueError("Cannot convert empty name into var.")
5934
5935    if isinstance(name, Expression):
5936        name = name.name
5937    return Var(this=name)
5938
5939
5940def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
5941    """Build ALTER TABLE... RENAME... expression
5942
5943    Args:
5944        old_name: The old name of the table
5945        new_name: The new name of the table
5946
5947    Returns:
5948        Alter table expression
5949    """
5950    old_table = to_table(old_name)
5951    new_table = to_table(new_name)
5952    return AlterTable(
5953        this=old_table,
5954        actions=[
5955            RenameTable(this=new_table),
5956        ],
5957    )
5958
5959
5960def convert(value: t.Any, copy: bool = False) -> Expression:
5961    """Convert a python value into an expression object.
5962
5963    Raises an error if a conversion is not possible.
5964
5965    Args:
5966        value: A python object.
5967        copy: Whether or not to copy `value` (only applies to Expressions and collections).
5968
5969    Returns:
5970        Expression: the equivalent expression object.
5971    """
5972    if isinstance(value, Expression):
5973        return maybe_copy(value, copy)
5974    if isinstance(value, str):
5975        return Literal.string(value)
5976    if isinstance(value, bool):
5977        return Boolean(this=value)
5978    if value is None or (isinstance(value, float) and math.isnan(value)):
5979        return NULL
5980    if isinstance(value, numbers.Number):
5981        return Literal.number(value)
5982    if isinstance(value, datetime.datetime):
5983        datetime_literal = Literal.string(
5984            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
5985        )
5986        return TimeStrToTime(this=datetime_literal)
5987    if isinstance(value, datetime.date):
5988        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
5989        return DateStrToDate(this=date_literal)
5990    if isinstance(value, tuple):
5991        return Tuple(expressions=[convert(v, copy=copy) for v in value])
5992    if isinstance(value, list):
5993        return Array(expressions=[convert(v, copy=copy) for v in value])
5994    if isinstance(value, dict):
5995        return Map(
5996            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
5997            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
5998        )
5999    raise ValueError(f"Cannot convert {value}")
6000
6001
6002def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6003    """
6004    Replace children of an expression with the result of a lambda fun(child) -> exp.
6005    """
6006    for k, v in expression.args.items():
6007        is_list_arg = type(v) is list
6008
6009        child_nodes = v if is_list_arg else [v]
6010        new_child_nodes = []
6011
6012        for cn in child_nodes:
6013            if isinstance(cn, Expression):
6014                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6015                    new_child_nodes.append(child_node)
6016                    child_node.parent = expression
6017                    child_node.arg_key = k
6018            else:
6019                new_child_nodes.append(cn)
6020
6021        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6022
6023
6024def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6025    """
6026    Return all table names referenced through columns in an expression.
6027
6028    Example:
6029        >>> import sqlglot
6030        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6031        ['a', 'c']
6032
6033    Args:
6034        expression: expression to find table names.
6035        exclude: a table name to exclude
6036
6037    Returns:
6038        A list of unique names.
6039    """
6040    return {
6041        table
6042        for table in (column.table for column in expression.find_all(Column))
6043        if table and table != exclude
6044    }
6045
6046
6047def table_name(table: Table | str, dialect: DialectType = None) -> str:
6048    """Get the full name of a table as a string.
6049
6050    Args:
6051        table: Table expression node or string.
6052        dialect: The dialect to generate the table name for.
6053
6054    Examples:
6055        >>> from sqlglot import exp, parse_one
6056        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6057        'a.b.c'
6058
6059    Returns:
6060        The table name.
6061    """
6062
6063    table = maybe_parse(table, into=Table)
6064
6065    if not table:
6066        raise ValueError(f"Cannot parse {table}")
6067
6068    return ".".join(
6069        part.sql(dialect=dialect, identify=True)
6070        if not SAFE_IDENTIFIER_RE.match(part.name)
6071        else part.name
6072        for part in table.parts
6073    )
6074
6075
6076def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6077    """Replace all tables in expression according to the mapping.
6078
6079    Args:
6080        expression: expression node to be transformed and replaced.
6081        mapping: mapping of table names.
6082        copy: whether or not to copy the expression.
6083
6084    Examples:
6085        >>> from sqlglot import exp, parse_one
6086        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6087        'SELECT * FROM c'
6088
6089    Returns:
6090        The mapped expression.
6091    """
6092
6093    def _replace_tables(node: Expression) -> Expression:
6094        if isinstance(node, Table):
6095            new_name = mapping.get(table_name(node))
6096            if new_name:
6097                return to_table(
6098                    new_name,
6099                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6100                )
6101        return node
6102
6103    return expression.transform(_replace_tables, copy=copy)
6104
6105
6106def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6107    """Replace placeholders in an expression.
6108
6109    Args:
6110        expression: expression node to be transformed and replaced.
6111        args: positional names that will substitute unnamed placeholders in the given order.
6112        kwargs: keyword arguments that will substitute named placeholders.
6113
6114    Examples:
6115        >>> from sqlglot import exp, parse_one
6116        >>> replace_placeholders(
6117        ...     parse_one("select * from :tbl where ? = ?"),
6118        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6119        ... ).sql()
6120        "SELECT * FROM foo WHERE str_col = 'b'"
6121
6122    Returns:
6123        The mapped expression.
6124    """
6125
6126    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6127        if isinstance(node, Placeholder):
6128            if node.name:
6129                new_name = kwargs.get(node.name)
6130                if new_name:
6131                    return convert(new_name)
6132            else:
6133                try:
6134                    return convert(next(args))
6135                except StopIteration:
6136                    pass
6137        return node
6138
6139    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6140
6141
6142def expand(
6143    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6144) -> Expression:
6145    """Transforms an expression by expanding all referenced sources into subqueries.
6146
6147    Examples:
6148        >>> from sqlglot import parse_one
6149        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6150        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6151
6152        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6153        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6154
6155    Args:
6156        expression: The expression to expand.
6157        sources: A dictionary of name to Subqueryables.
6158        copy: Whether or not to copy the expression during transformation. Defaults to True.
6159
6160    Returns:
6161        The transformed expression.
6162    """
6163
6164    def _expand(node: Expression):
6165        if isinstance(node, Table):
6166            name = table_name(node)
6167            source = sources.get(name)
6168            if source:
6169                subquery = source.subquery(node.alias or name)
6170                subquery.comments = [f"source: {name}"]
6171                return subquery.transform(_expand, copy=False)
6172        return node
6173
6174    return expression.transform(_expand, copy=copy)
6175
6176
6177def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6178    """
6179    Returns a Func expression.
6180
6181    Examples:
6182        >>> func("abs", 5).sql()
6183        'ABS(5)'
6184
6185        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6186        'CAST(5 AS DOUBLE)'
6187
6188    Args:
6189        name: the name of the function to build.
6190        args: the args used to instantiate the function of interest.
6191        dialect: the source dialect.
6192        kwargs: the kwargs used to instantiate the function of interest.
6193
6194    Note:
6195        The arguments `args` and `kwargs` are mutually exclusive.
6196
6197    Returns:
6198        An instance of the function of interest, or an anonymous function, if `name` doesn't
6199        correspond to an existing `sqlglot.expressions.Func` class.
6200    """
6201    if args and kwargs:
6202        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6203
6204    from sqlglot.dialects.dialect import Dialect
6205
6206    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6207    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6208
6209    parser = Dialect.get_or_raise(dialect)().parser()
6210    from_args_list = parser.FUNCTIONS.get(name.upper())
6211
6212    if from_args_list:
6213        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6214    else:
6215        kwargs = kwargs or {"expressions": converted}
6216        function = Anonymous(this=name, **kwargs)
6217
6218    for error_message in function.error_messages(converted):
6219        raise ValueError(error_message)
6220
6221    return function
6222
6223
6224def true() -> Boolean:
6225    """
6226    Returns a true Boolean expression.
6227    """
6228    return Boolean(this=True)
6229
6230
6231def false() -> Boolean:
6232    """
6233    Returns a false Boolean expression.
6234    """
6235    return Boolean(this=False)
6236
6237
6238def null() -> Null:
6239    """
6240    Returns a Null expression.
6241    """
6242    return Null()
6243
6244
6245# TODO: deprecate this
6246TRUE = Boolean(this=True)
6247FALSE = Boolean(this=False)
6248NULL = Null()